CODEWITHSUNDEEP
powerbidax
TheHaso
TheHaso

Reputation: 25

How to count when string value filtered by A is also equal to string value filtered by B

I am currently working in Power BI and need to write the DAX code for counting number of string values when string filtered by A is also appearing when same string filtered by B.

I have tried various of codes and everything that came in my mind, but I am lacking experience in comparing values in DAX, and kinda beat at the moment.

eg.
enter image description here

When looking at table, I need to count (distinct) when John, Adam, Julie from A is also appearing in B, which is in this case is 3.

I will be happy if someone has any ideas or directions for me to seek into!

Upvotes: 1

Views: 753

Answers (2)

RADO
RADO

Reputation: 8148

Assuming your table name is "Data":

 Count of Common Names   =
    VAR A_Names =
        CALCULATETABLE ( VALUES ( Data[Name] ), Data[Filter] = "A" )
    VAR B_Names =
        CALCULATETABLE ( VALUES ( Data[Name] ), Data[Filter] = "B" )
    VAR Common_Names =
        INTERSECT ( A_Names, B_Names )
    RETURN
        COUNTROWS ( Common_Names )

How it works: First, we create a table of distinct names for filter A. Second, we do the same for filter B. Finally, we find what names exist in both tables, by finding their intersection.

Edit:

To calculate costs for common names, modify the above measure:

Cost of A given B =
VAR A_Names =
    CALCULATETABLE ( VALUES ( Data[Name] ), Data[Filter] = "A" )
VAR B_Names =
    CALCULATETABLE ( VALUES ( Data[Name] ), Data[Filter] = "B" )
VAR Common_Names =
    INTERSECT ( A_Names, B_Names )
RETURN
    CALCULATE ( SUM ( Data[Cost] ), Common_Names, Data[Filter] = "A" )

Upvotes: 1

Olly
Olly

Reputation: 7891

This will count the number of distinct names, having more than one distinct value:

Names with Multiple Distinct Values = 
COUNTROWS ( 
    FILTER ( 
        SUMMARIZECOLUMNS ( 
            MyTable[Name], 
            "Distinct Values", 
            DISTINCTCOUNT ( MyTable[Values] )
        ),
        [Distinct Values] > 1
    )
)

Upvotes: 0

Related Questions