Include based on slicer

[Jyaul1122]

Hello All,

I have two tables dimenstion and fact :

Which are related with Project column(one to many). And I have  Project  slicer (from Dimension table).

I wrote measure: Risk value=max(Fact table(Risk value).

What I need, when there is no selection in Project slicer it will display all Project like:

If there is  Project slicer selected, there result will be rows of that project but keep also row for Include(A/B) Project for that District:

For example: if I selected Project P1 from slicer the result will be: 

 if Project P1 and P2 are selected then result will be:

same way If project P3 or P4 will be selected from District B then its needs to include Project include Project B

If P1 and P3 are selected then: 

means whenever any project are selected from slicer then its need to bring also Project Include ( A or B) for that District.

How can I achieve using meausres ?

Sample data:

Dimension Table
District	Project
A	P1
A	P2
A	Include A
B	P3
B	P4
B	Include B

FACT Table
District	Project	Month	Risk Value
A	P1	25-Jan	1
A	P2	25-Feb	2
B	P3	25-Feb	3
B	P4	25-Mar	3
A	P1	25-Apr	4
A	Include A	25-May	2
B	Include B	25-Aug	3

[v-dineshya]

Hi @Jyaul1122 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @FBergamaschi , Thank you for your prompt response.

Hi @Jyaul1122 ,  Could you please try the proposed solution shared by @FBergamaschi .  Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[FBergamaschi]

To obtain what you are looking for, there are two ways:

1 - all in DAX

2 - getting help from the tables

To make it fast, I choosed option 2, here is the file for you to inspect

https://drive.google.com/drive/folders/1ygPYgruaZZy8Spvekfe4j6qd_N2eng7n?usp=sharing

If you need a full DAX solution keeping your tables, I shall look into that, I think it is feasible but will need a bit more time

Best

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Jyaul1122]

@FBergamaschi 

I would appreciated via DAX. Thanks

[FBergamaschi]

Done, same location folder

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Jyaul1122]

@FBergamaschi 

Thanks for your reply but I can not write condition for more than 200 unique Projects.

VAR Selection = VALUES ( Dim[Project] )

VAR SelectionExpandedA = UNION( Selection, {"Include A"} )

VAR SelectionExpandedB = UNION( Selection, {"Include B"} )

VAR SelectionExpandedAB = UNION( Selection, {"Include A"},{"Include B"}  )

VAR Result=

IF (

    ("P1" IN Selection || "P2" IN Selection) && ("P3" IN Selection || "P4" IN Selection),

    CALCULATE( MAX ( Facts[Risk Value] ), KEEPFILTERS(Facts[Project] IN SelectionExpandedAB )),

    IF (

          "P1" IN Selection || "P2" IN Selection,

CALCULATE( MAX ( Facts[Risk Value] ), KEEPFILTERS(Facts[Project] IN SelectionExpandedA ) ),

IF (

          "P3" IN Selection || "P4" IN Selection,

          CALCULATE( MAX ( Facts[Risk Value] ), KEEPFILTERS(Facts[Project] IN SelectionExpandedB ))

)

    ))

    RETURN Result

Could you please do needfull ? Thanks

[FBergamaschi · Accepted solution]

Yes I can

You need a table like this though,

File again in the same location

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI