Apply one slicer to different columns

[oliverblane]

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!

[VahidDM · Accepted solution]

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/

[oliverblane]

This worked perfectly - thank you @VahidDM!

[CNENFRNL]

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

[oliverblane]

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?

[CNENFRNL]

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.

[VahidDM]

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/

[oliverblane]

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.

[VahidDM]

@oliverblane 

Did you test my code for math card?

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

[oliverblane]

Hi @VahidDM.

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