topic Re: Apply one slicer to different columns in DAX Commands and Tips

Apply one slicer to different columns

oliverblane — Thu, 25 Nov 2021 12:17:02 GMT

I have a table similar to the following:

My aim is to have cards that show the number of assessments where the "Days" column is <= 5, with a slicer on the level (Levels 1/2/3). I have a separate Level table that I have built relationships to each of the Maths/English/Science Level columns which I slice on:

The model looks like the following:

Where the active relationship is Level -> English Level.

My DAX formulas are like:

Maths = CALCULATE(COUNT('Data'[Maths Level]), FILTER('Data', 'Data'[Maths Days] <= 5), USERELATIONSHIP('Data'[Maths Level],'Slicer'[Level])) + 0

and similar for the English and Science. However, only the English measure has the expected values when applying the slicer "Level". Am I doing something wrong? I thought that USERELATIONSHIP would make the chosen inactive relationship become active?

Sample file: https://1drv.ms/u/s!AsgvTV9XMzI1hYRb7VJx2OFLw6_z4g

Thank you for reading!

Re: Apply one slicer to different columns

VahidDM — Thu, 25 Nov 2021 11:31:32 GMT

Hi oliverblane

What is that "'Learner Info Daily" table??

Try this:

Maths = CALCULATE( COUNT( 'Data'[Maths Level] ), FILTER( ALLEXCEPT('Data','Data'[Maths Level]), 'Data' <= 5 ), USERELATIONSHIP ( 'Data'[Maths Level], 'Slicer'[Level] ) ) + 0

if it does not work:

Can you post sample data as text and expected output? or share a PBIX [Power BI file] after removing sensetive data?

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Apply one slicer to different columns

oliverblane — Thu, 25 Nov 2021 12:16:20 GMT

Hi VahidDM, thank you for your reply.

https://1drv.ms/u/s!AsgvTV9XMzI1hYRb7VJx2OFLw6_z4g

I have attached a PBIX file with sample data. When no slicer is selected, the output of the 3 cards should be Maths=5, English=6, Science=7, but as you will see, only English is correct. This is the same when the slicers are selected, for example when Level 1 is selected, the cards should show Maths=1, English=3, Science=2.

(Also, the learner info daily table was me forgetting to change a table name when writing the question - sorry!).

Thanks for your help.

Re: Apply one slicer to different columns

VahidDM — Thu, 25 Nov 2021 12:43:55 GMT

oliverblane

Did you test my code for math card?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Apply one slicer to different columns

oliverblane — Thu, 25 Nov 2021 13:16:59 GMT

Hi VahidDM.

Yes, I tried that code but unfortunately all it did was stop the measure from changing with the slicer.

Re: Apply one slicer to different columns

CNENFRNL — Thu, 25 Nov 2021 21:14:19 GMT

Transform your dataset in "a DAX way", then all things get much easier,

Re: Apply one slicer to different columns

VahidDM — Thu, 25 Nov 2021 22:51:30 GMT

Hi oliverblane

Try these measures:

English = CALCULATE( COUNT( 'Data'[English Level] ), FILTER( Data, 'Data'[English Days] <= 5 ), USERELATIONSHIP ( 'Data'[English Level], 'Slicer'[Level] ) ) + 0

Maths = CALCULATE( COUNT( 'Data'[Maths Level] ), 'Data'[Maths Days] <= 5, REMOVEFILTERS(Data[English Level]), USERELATIONSHIP ( 'Data'[Maths Level], 'Slicer'[Level] ) ) + 0

Science = CALCULATE( COUNT( 'Data'[Science Level] ), 'Data'[Science Days] <= 5 ,REMOVEFILTERS(Data[English Level]), USERELATIONSHIP ( 'Data'[Science Level], 'Slicer'[Level] ) )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Apply one slicer to different columns

oliverblane — Fri, 26 Nov 2021 08:12:06 GMT

This worked perfectly - thank you VahidDM!

Re: Apply one slicer to different columns

oliverblane — Fri, 26 Nov 2021 08:17:12 GMT

Thank you for your reply CNENFRNL, I agree that having the data in that format makes things a lot easier. How would I transform my original dataset to that format using DAX?

Re: Apply one slicer to different columns

CNENFRNL — Fri, 26 Nov 2021 11:04:16 GMT

I doubt it; even if possible, the complexity is beyond imagination. Above all, DAX isn't designed for ETL process in nature; all transformation of dataset is supposed to be conducted in PQ.