Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
al4x
Frequent Visitor

Perform a subtraction on a DISTINCT COUNT based on a 'Filter'

Tearing my hair out on this.

 

I have a table with many rows that when a distinct count is performed across the first pair of columns produces the result:

 

A|Z|15

A|Y|10
B|Y|1

A|X|6

A|W|10

B|W|2

 

ie. above, there are 15 record in the original table with A|Z|.

 

 

But i want to subtract the distinct count for 'B' from 'A'

Thus the |A|W| beciomes 8 rather than 10. See below.

 

A|Z|15

A|Y|9
B|Y|1

A|X|6

A|W|8

B|W|2

 

 

This needs to be done depending on slicers, so i cant do it with an excluding join in the SQL query.

Im not very good with DAX, and to date i have gotten by with doing this sort of thing in SQL queries.

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

Hi @al4x,

 

Based on my test, you should be able to use the formula below to create a new measure to subtract the distinct count for 'B' from 'A' in your scenario.

Measure = 
VAR currentColumn1 =
    FIRSTNONBLANK ( Table1[Column1], 1 )
VAR currentColumn2 =
    FIRSTNONBLANK ( Table1[Column2], 1 )
RETURN
    IF (
        currentColumn1 = "A",
        DISTINCTCOUNT ( Table1[Column3] )
            - CALCULATE (
                DISTINCTCOUNT ( Table1[Column3] ),
                FILTER (
                    ALLSELECTED ( Table1 ),
                    Table1[Column1] = "B"
                        && Table1[Column2] = currentColumn2
                )
            ),
        DISTINCTCOUNT ( Table1[Column3] )
    )

r1.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

1 REPLY 1
v-ljerr-msft
Employee
Employee

Hi @al4x,

 

Based on my test, you should be able to use the formula below to create a new measure to subtract the distinct count for 'B' from 'A' in your scenario.

Measure = 
VAR currentColumn1 =
    FIRSTNONBLANK ( Table1[Column1], 1 )
VAR currentColumn2 =
    FIRSTNONBLANK ( Table1[Column2], 1 )
RETURN
    IF (
        currentColumn1 = "A",
        DISTINCTCOUNT ( Table1[Column3] )
            - CALCULATE (
                DISTINCTCOUNT ( Table1[Column3] ),
                FILTER (
                    ALLSELECTED ( Table1 ),
                    Table1[Column1] = "B"
                        && Table1[Column2] = currentColumn2
                )
            ),
        DISTINCTCOUNT ( Table1[Column3] )
    )

r1.PNG

 

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.