0

I have two tables are Data and Report.

Data Table:

In Data table contain three columns are Item, status, and filter.

The item contains duplicated entry and the item column contains text and number or number only or text only.

The status column contains two different text/comments, "Okay" and "Not Okay"

The filter column contains two different filters which are A1 and A2.

The report table

In the Report table, I updated both comments/text as "Okay" or "Not Okay". I am looking for count against filter A1 and A2 according to the comments.

I would like to create a new calculated column in the report table in order to get the unique count according to the comments and filter based on the data table columns item and status.

DATA:

enter image description here

REPORT

enter image description here

Alexis Olson helped the following calculated column in order to get the unique count. I am trying to add one more filter in existing DAX calculated column but it's not working. Can you please advise?

1.Desired Result =
VAR Comment = REPORT[COMMENTS]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( DATA[ITEM] ),
        DATA[STATUS] = Comment
    )
    
2.Desired Result =
COUNTROWS (
    SUMMARIZE (
        FILTER ( DATA, DATA[STATUS] = REPORT[COMMENTS] ),
        DATA[ITEM]
    )
)

3.Desired Result =
SUMX (
    DISTINCT ( DATA[ITEM] ),
    IF ( CALCULATE ( SELECTEDVALUE ( DATA[STATUS] ) ) = REPORT[COMMENTS], 1, 0 )
)
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
johon
  • 65
  • 3
  • 12

1 Answers1

1

I think you can just add a filter to CALCULATE:

Filter by A1 Result =
VAR Comment = REPORT[COMMENTS]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( DATA[ITEM] ),
        DATA[STATUS] = Comment,
        DATA[FILTER] = "A1"
    )

For the second method,

Filter by A1 Result =
COUNTROWS (
    SUMMARIZE (
        FILTER ( DATA, DATA[STATUS] = REPORT[COMMENTS] && REPORT[FILTER] = "A1" ),
        DATA[ITEM]
    )
)

I do not recommend using the third one but it would be like this

Filter by A1 Result =
SUMX (
    DISTINCT ( DATA[ITEM] ),
    IF (
        CALCULATE ( SELECTEDVALUE ( DATA[STATUS] ) ) = REPORT[COMMENTS]
            && CALCULATE ( SELECTEDVALUE ( DATA[FILTER] ) ) = "A1",
        1,
        0
    )
)
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • thanks for your help and advise. It's working well. Can you please advise how can I add filter column the following two formulas please. 2.Desired Result = COUNTROWS ( SUMMARIZE ( FILTER ( DATA, DATA[STATUS] = REPORT[COMMENTS] ), DATA[ITEM] ) ) 3.Desired Result = SUMX ( DISTINCT ( DATA[ITEM] ), IF ( CALCULATE ( SELECTEDVALUE ( DATA[STATUS] ) ) = REPORT[COMMENTS], 1, 0 ) ) – johon Jan 22 '21 at 15:59
  • I'd recommend just using the first one but see my edit. – Alexis Olson Jan 22 '21 at 16:05
  • Thank you so much for your quick response. I will follow up your advise. – johon Jan 22 '21 at 16:16