Filtering Rows with measure result

[Anonymous]

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!!

[Anonymous · Accepted solution]

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

[Anonymous]

Thanks a lot Xiaoxin,

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