select coulmns as per slicer value

[Anonymous]

Hi Team,

I have 2 tables in the backend: 

Country	Compliant Full
US	4
Ind	11

Country	Year	Compliant Year
US	2020	3
US	2021	0
Ind	2020	1
Ind	2021	2

I have simple power bi report in which there are 2 slicers : Country and Year and in table visual i wanted to show 2 columns 

Country and Compliant

 when user filter only on Country then 2nd column should show value from Compliant full and when user chooses Country + Year or Just year then 2nd column should show value from Compliant Year. 

I used belwo DAX measure Compliant = IF(HASONEVALUE(Table2(Year), SUM(Compliant year), SUM(Compliant Full)) 

But it is giving me incorrect result in the Total of the table. 

Could you please help me on this ?

Thanks,

Shubham

[Anonymous]

It worked !! Thank you so much bhai !!

[amitchandak]

@Anonymous , Please accept the appropriate solution.

[ryan_mayu]

@Anonymous 

maybe you can try this

Measure = if(ISFILTERED('Table (2)'[Year]),SUM('Table (2)'[Compliant Year]),sum('Table'[Compliant Full]))

please see the attachment below

[amitchandak]

@Anonymous , First on all create a common table country and use that in visual and have measure like

Country = distinct(Distinct(Table[Country]), distinct(Table2[country]))

https://youtu.be/Bkf35Roman8

sumx(Country, IF(HASONEVALUE(Table2(Year)), SUM(Table2[Compliant year]), SUM(Table1[Compliant Full])))

[Anonymous]

@amitchandak Yes..but the total is not working 

[amitchandak]

@Anonymous , Try this small change

sumx(values(Country[country]), IF(HASONEVALUE(Table2(Year)), SUM(Table2[Compliant year]), SUM(Table1[Compliant Full])))

[Anonymous]

No. Still it is giving same result. 

[amitchandak · Accepted solution]

@Anonymous , Problem That tell why we need calculate in SUMX

Measure = sumx(values(Country[country]), CALCULATE(IF(HASONEVALUE(Table2[Year]), SUM(Table2[Compliant year]), SUM(Table1[Compliant Full]))))

Please find the file after signature