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
Anonymous
Not applicable

Filtering Rows with measure result

Hi guys, 

I need your ideas in a topic. I am new with BI (relatively I have a 3 months experience more or less) and I would like to make the above calculation.

I have a dataset with 5 columns:

Date,        ID_num,     TN,       Level, Category

1/1/2019, 111111, 00112233, Level1, CatA

1/2/2019, 111112, 00112234, Level1, CatB

2/2/2019, 111113, 00112234, Level1, CatB

3/1/2019, 111114, 00112235, Level2, CatC

3/2/2019, 111115, 00112236, Level1, CatB

4/2/2019, 111116, 00112233, Level1, CatB

5/2/2019, 111117, 00112237, Level2, CatA

5/2/2019, 111113, 00112238, Level1, CatB

5/1/2019, 111114, 00112239, Level2, CatC

6/2/2019, 111115, 00112240, Level1, CatB

7/2/2019, 111116, 00112241, Level1, CatB

 

I would like construct the below calculations

 

a) Count the ID_nums, by TN, Level and Category. --> Basically Group by TN, Level, Category and count ID_nums for dynamicaly selected Date (have a relative slicer that will keep the last X days for example).

b) After that I want to distinct count all TNs in which the above calculation on ID_nums per Level, Category is more than 1.

c) and finally divide the above count of TNs by the total distinct TNs in order to produce a Success Rate %.

 

Until now I have done a reference table (power query environment) with group by Date, TN, Level, Category, countof(ID_num). Then in Power BI environment I am trying to sum the new column countof(ID_num) for the period I want by appling a slicer in order to set something like a filter measure to use it in order to distinct count only the TNs that are agree with the filter, And then somehow divide that number with the total TNs by Level, Category always. Unfortunatelly nothing seems to work

 

And all that calculations to be applied every time I change the date period. 

 

Can anybody help me??

Thanks in advance all of you!!

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @Anonymous ,

#1. Count id based on tn, level, category group.

Count ID=
CALCULATE (
    COUNT ( Table[ID] ) ,
    ALLSELECTED ( Table ),
    VALUES ( Table[TN] ),
    VALUES ( Table[Level] ),
    VALUES ( Table[Category] )
)

#2, Distinct TN count:

DC TN =
VAR summary =
    SUMMARIZE ( Table, [TN], [Level], [Category], "Count", COUNT ( Table[ID] ) )
RETURN
    COUNTROWS (
        DISTINCT ( SELECTCOLUMNS ( FILTER ( Summary, [Count] > 1 ), "TN", [TN] ) )
    )

#3. Success percent:

Success= [Count ID]/[DC TN]

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @Anonymous ,

#1. Count id based on tn, level, category group.

Count ID=
CALCULATE (
    COUNT ( Table[ID] ) ,
    ALLSELECTED ( Table ),
    VALUES ( Table[TN] ),
    VALUES ( Table[Level] ),
    VALUES ( Table[Category] )
)

#2, Distinct TN count:

DC TN =
VAR summary =
    SUMMARIZE ( Table, [TN], [Level], [Category], "Count", COUNT ( Table[ID] ) )
RETURN
    COUNTROWS (
        DISTINCT ( SELECTCOLUMNS ( FILTER ( Summary, [Count] > 1 ), "TN", [TN] ) )
    )

#3. Success percent:

Success= [Count ID]/[DC TN]

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Thanks a lot Xiaoxin,

This code seems to work pretty well my friend!!! Awesome!!!

 

 

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.