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
pbhat89
Helper II
Helper II

DAX hierarchical complex calculations

I have working on something for long and have related queries (https://community.powerbi.com/t5/Desktop/Conditional-tables-using-DAX-or-other-methods/m-p/1542696#M...) and (https://community.powerbi.com/t5/Desktop/Cross-filter-to-exclude/m-p/1452395#M608419) - while some tips have helped however i haven't managed to get to the final outcome. I suspect its my lack of explaining of expected outcome. hence providing below a detail of all the measures that i need as i have established ( dynamic tables / temp tables are not possible. It is also not possible to use what if parameter in calculation of a new column ). Hence below is details of data and expected measures : 

 

Table 1 ( Each row represents unique Case ID and Life ID ) - this is the main table 

 

Case IDLife IDAgeBMIRandFlagDecLength
1A45230A0.83
1B54320A0.78
2C32291A0.9
3D32230CA0.35
3E31270CA0.89
3F15221CA0.99
4G45300A0.95
5H24280A0.91
6I53250CA0.12
6J51260CA0.97
7K32251A0.34
7L35240A0.35
8M34290A0.99
9N31250D0.23

 

Table 2 - condition table . This also represents case ID and life ID per row however one case and Life ID can repeat if they have multiple conditions. the case IDs and Life IDs are a subset of above and do not have to inlcude all. 

 

Case IDLife IDCondition
3EX1
6IX2
1BX3
3DX2

 

What if Parameter 

Threshold = From (0,1) 

 

FILTERS

 

Filters are applied based on Age , BMI and Conditions(table2)

lets take the below filters for our example 

 

 AgeBMICondition
Filters30-5018-31X1

while Age and BMI work by filtering out the cases which don't fall in that range - X1 is suppose to work the excluding the case associated with X1 i.e. Case ID 3. More explained below. 

 

threshold = 0.90 for this example 

 

Expected Measures / calculations 

 

Note that below calculations are in an order like a flow chart. each stage is a subset of other 

 

1) Count Case ID (Distinct) : this is simplest. Distinct count of Case ID from table 1 = 9

 

2a) Count_CaseID_filtered (Distinct) : Count of case IDs which have been filtered out (due to 1 or more reason ) =4 , [Case ID - 1,3,5,6 as 1B is kicked out due to Age & even BMI , 3E is kicked out due to BMI and condition , 3F is kicked out due to Age, 5H and 6J are kicked out due to age]. While case and Life are filtered out - we count the whole case as filtered out, hence distinct count is 4

 

2b) Count_CaseID_not_filtered (Distinct) = 5 = whatever Case ID was not filtered out is left to count here i.e. 2,4,7,8,9. note that while say 1A may not be filtered out but since 1b was filtered out - whole case 1 gets filtered out and is not expected to be in the counts. 

 

2a + 2b should always = 1

 

2a.1) % A Rate_filtered = of the cases filtered out [1,3,5,6] how many have Dec = A given as % of that group total : Answer = 2 [case Ids1,5]/4 = 0.5 

 

2b.1) Count_CaseID_RandFlag (Distinct) : of the non filtered cases [2,4,7,8,9] how many have RandFlag = 1 : Answer = 2 , Cases 2,7 

2b.1.1) % A Rate_rand_flag = of the cases [2,7] how many have Dec = A given as % of that group total : Answer = 2[both 2,7 are A]/2 = 1

 

2b.2) Count_CaseID_NonRandFlag (Distinct) : Count of the cases which are non filtered and non Rand flag i.e. 4,8,9 = 3

2b.2.1) % A Rate_nonrand_flag = of the cases [4,8,9] how many have Length>= Threshold(what if parameter set at 0.9) given as % of that group total : Answer = 2[both 4,8 are >=0.9]/3 = 0.66

 

2b.1 + 2b.2 should always = 2b

 

 

3) Overall Rate % = coount of cases which were A in Filtered () + count of cases which were A in RandFlag + count of cases which were >= threshold in NonRandFlag / total cases : Answer = 2+2+2/9 = 6/9 = 0.66

2 ACCEPTED SOLUTIONS

Hi @pbhat89 
I build a sample like yours to have a test.

Main Table:

2.png

Condition Table:

3.png

I build an Age table ,a BMI table and parameter for slicer.

Age = GENERATESERIES(MIN('Main table'[Age]),MAX('Main table'[Age]),1)
BMI = GENERATESERIES(MIN('Main table'[BMI]),MAX('Main table'[BMI]),1)
Parameter = GENERATESERIES(0, 1, 0.1)

Due to there are several measures, I will show you some of them.

2a) Count_CaseID_filtered (Distinct) = 
VAR _CaseIDCondition =
    SUMMARIZE (
        FILTER (
            ALL ( 'Conditon' ),
            'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
        ),
        'Conditon'[Case ID]
    )
VAR _Age =
    GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
    GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
    SUMMARIZE (
        FILTER (
            ALL ( 'Main table' ),
            NOT ( 'Main table'[Age] IN _Age )
                || NOT ( 'Main table'[BMI] IN _BMI )
                || 'Main table'[Case ID] IN _CaseIDCondition
        ),
        'Main table'[Case ID]
    )
RETURN
    COUNTAX ( _CaseIDMain, [Case ID] )
2a.1) % A Rate_filtered = 
VAR _CaseIDCondition =
    SUMMARIZE (
        FILTER (
            ALL ( 'Conditon' ),
            'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
        ),
        'Conditon'[Case ID]
    )
VAR _Age =
    GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
    GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
    SUMMARIZE (
        FILTER (
            ALL ( 'Main table' ),
            AND (
                NOT ( 'Main table'[Age] IN _Age )
                    || NOT ( 'Main table'[BMI] IN _BMI )
                    || 'Main table'[Case ID] IN _CaseIDCondition,
                'Main table'[Dec] = "A"
            )
        ),
        'Main table'[Case ID]
    )
VAR _CountIDDecA =
    COUNTX ( _CaseIDMain, [Case ID] )
RETURN
    DIVIDE ( _CountIDDecA, [2a) Count_CaseID_filtered (Distinct)] )
2b.1) Count_CaseID_RandFlag (Distinct) = 
VAR _CaseIDCondition =
    SUMMARIZE (
        FILTER (
            ALL ( 'Conditon' ),
            'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
        ),
        'Conditon'[Case ID]
    )
VAR _Age =
    GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
    GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
    SUMMARIZE (
        FILTER (
            ALL ( 'Main table' ),
            NOT ( 'Main table'[Age] IN _Age )
                || NOT ( 'Main table'[BMI] IN _BMI )
                || 'Main table'[Case ID] IN _CaseIDCondition
        ),
        'Main table'[Case ID]
    )
VAR _Casenotfilter =
    SUMMARIZE (
        FILTER (
            'Main table',
            NOT ( 'Main table'[Case ID] )
                IN _CaseIDMain
                && 'Main table'[RandFlag] = 1
        ),
        'Main table'[Case ID]
    )
RETURN
    COUNTAX ( _Casenotfilter, [Case ID] )

And so on.

Result is as below.

1.png

You can download the pbix file from this link: DAX hierarchical complex calculations

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

View solution in original post

Hi @pbhat89 

You can try to build a "Feature" Table as a new slicer table.

And then use it to update your measures.

If you want to select single Feature in slicer you can use selectedvalue in dax , if you want to select multiple values in slicer you can try values in dax.

Then add a filter in measure.

like 

 

VAR _FeatureID = 
SUMMARIZE (
        FILTER (
            ALL ( 'Main table' ),
            'Main table'[Feature] in VALUES(Feature[Feature])
        ),
        'Main table'[Case ID]
    )

 

Then use this filter in below Maintable filters to calculate the count.

like

 Var _Result = 
     countx(Filter(All('Main Table'),filter1&&filter2&&...&&Max('Main table'[Case ID])in _FeatureID))
...

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 



View solution in original post

8 REPLIES 8
v-rzhou-msft
Community Support
Community Support

Hi @pbhat89 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

 

Best Regards,

Rico Zhou 

v-rzhou-msft
Community Support
Community Support

Hi @pbhat89 

Your filters in example may be Age from 30 to 50, BMI from 18 to 31, conditon = X1 , threshold > 0.90 for this example.

Here I have some questions:

1. Did you build relationships by Case ID columns in two tables?

2. How did you filter your tables, by slicers or filter fields?

If I relate two tables and I use this filter as above. It will only show blank,

1.png

So if I use your filter, your measure seems that won't show results you want.

And the smallest number in your sample BMI is 22, how could you get 18 to 31?

If I have any misunderstandings, please show me more details or samples.

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Rico,

 

1. Did you build relationships by Case ID columns in two tables? -- No i didn't. To get some of my measures i used Case ID 'not' in' and isfiltered commands to get my results. I understand it may not be the best way. The idea is for this to work as an exclude (not include filter) hence i only got to this approach to make it work.

 

2.  How did you filter your tables, by slicers or filter fields? --- if i keep as slicers - i can't created a new column say Filter_age = T/F as What if parameters can't be used to create columns , only measures. I used filters directly.

 

3. Please correct BMI as 22-31. you are right, i created that mistake while creating this data sample. 

 

Not sure what your table doesn't show. I suspect some error. As you can see from the data -- with Filters of Age , BMI and X1(case ID 3 filtered out) = there are cases left (2,4,7,8,9)

 

 

Hi @pbhat89 
I build a sample like yours to have a test.

Main Table:

2.png

Condition Table:

3.png

I build an Age table ,a BMI table and parameter for slicer.

Age = GENERATESERIES(MIN('Main table'[Age]),MAX('Main table'[Age]),1)
BMI = GENERATESERIES(MIN('Main table'[BMI]),MAX('Main table'[BMI]),1)
Parameter = GENERATESERIES(0, 1, 0.1)

Due to there are several measures, I will show you some of them.

2a) Count_CaseID_filtered (Distinct) = 
VAR _CaseIDCondition =
    SUMMARIZE (
        FILTER (
            ALL ( 'Conditon' ),
            'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
        ),
        'Conditon'[Case ID]
    )
VAR _Age =
    GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
    GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
    SUMMARIZE (
        FILTER (
            ALL ( 'Main table' ),
            NOT ( 'Main table'[Age] IN _Age )
                || NOT ( 'Main table'[BMI] IN _BMI )
                || 'Main table'[Case ID] IN _CaseIDCondition
        ),
        'Main table'[Case ID]
    )
RETURN
    COUNTAX ( _CaseIDMain, [Case ID] )
2a.1) % A Rate_filtered = 
VAR _CaseIDCondition =
    SUMMARIZE (
        FILTER (
            ALL ( 'Conditon' ),
            'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
        ),
        'Conditon'[Case ID]
    )
VAR _Age =
    GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
    GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
    SUMMARIZE (
        FILTER (
            ALL ( 'Main table' ),
            AND (
                NOT ( 'Main table'[Age] IN _Age )
                    || NOT ( 'Main table'[BMI] IN _BMI )
                    || 'Main table'[Case ID] IN _CaseIDCondition,
                'Main table'[Dec] = "A"
            )
        ),
        'Main table'[Case ID]
    )
VAR _CountIDDecA =
    COUNTX ( _CaseIDMain, [Case ID] )
RETURN
    DIVIDE ( _CountIDDecA, [2a) Count_CaseID_filtered (Distinct)] )
2b.1) Count_CaseID_RandFlag (Distinct) = 
VAR _CaseIDCondition =
    SUMMARIZE (
        FILTER (
            ALL ( 'Conditon' ),
            'Conditon'[Condition] = SELECTEDVALUE ( 'Conditon'[Condition] )
        ),
        'Conditon'[Case ID]
    )
VAR _Age =
    GENERATESERIES ( MIN ( Age[Age] ), MAX ( Age[Age] ) )
VAR _BMI =
    GENERATESERIES ( MIN ( BMI[BMI] ), MAX ( BMI[BMI] ) )
VAR _CaseIDMain =
    SUMMARIZE (
        FILTER (
            ALL ( 'Main table' ),
            NOT ( 'Main table'[Age] IN _Age )
                || NOT ( 'Main table'[BMI] IN _BMI )
                || 'Main table'[Case ID] IN _CaseIDCondition
        ),
        'Main table'[Case ID]
    )
VAR _Casenotfilter =
    SUMMARIZE (
        FILTER (
            'Main table',
            NOT ( 'Main table'[Case ID] )
                IN _CaseIDMain
                && 'Main table'[RandFlag] = 1
        ),
        'Main table'[Case ID]
    )
RETURN
    COUNTAX ( _Casenotfilter, [Case ID] )

And so on.

Result is as below.

1.png

You can download the pbix file from this link: DAX hierarchical complex calculations

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Rico - this is immensely helpful. I am now going through the structure and logic of forumales and trying to understand the flow. Just one comment - apart from Numeric fields as filters - what if i had a categorical filter in Main table : 

 

Case ID Life ID Age BMI RandFlag Dec Length Feature
1 A 45 23 0 A 0.83 G1
1 B 54 32 0 A 0.78 G1
2 C 32 29 1 A 0.9 G2
3 D 32 23 0 CA 0.35 G1
3 E 31 27 0 CA 0.89 G2
3 F 15 22 1 CA 0.99 G1
4 G 45 30 0 A 0.95 G1
5 H 24 28 0 A 0.91 G1
6 I 53 25 0 CA 0.12 G1
6 J 51 26 0 CA 0.97 G1
7 K 32 25 1 A 0.34 G3
7 L 35 24 0 A 0.35 G1
8 M 34 29 0 A 0.99 G1
9 N 31 25 0 D 0.23 G1


and in addition to Age and BMI - this is additional filter "Feature" - i.e. if G1 is selected , rest are filtered out. so in the above 4,8,9 are left in Count_CaseID_not_filtered (Distinct) as 2 has G2 and 7K has G3

Hi @pbhat89 

I think your problem is when you use slicer (Feature), your table will be filtered and your measure result is incorrect.

You can try to use all function in your measure to calculate on the all table instead of the filtered table.

You may refer to this blog: Manage "all" function

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Not quite sure how that addressed the Categorical feature as aspect. i am half way through implementing the above formulaes as per your recommendation. Are you saying if there is a new filter column such as "feature" which is categorical that i can't use the above? I have explored using ALL but hasn't solved the issue. what modifications to above to incorporate "Feature" also as a filter - can it be made a seperate table and then used in the same way?

Hi @pbhat89 

You can try to build a "Feature" Table as a new slicer table.

And then use it to update your measures.

If you want to select single Feature in slicer you can use selectedvalue in dax , if you want to select multiple values in slicer you can try values in dax.

Then add a filter in measure.

like 

 

VAR _FeatureID = 
SUMMARIZE (
        FILTER (
            ALL ( 'Main table' ),
            'Main table'[Feature] in VALUES(Feature[Feature])
        ),
        'Main table'[Case ID]
    )

 

Then use this filter in below Maintable filters to calculate the count.

like

 Var _Result = 
     countx(Filter(All('Main Table'),filter1&&filter2&&...&&Max('Main table'[Case ID])in _FeatureID))
...

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 



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.