Forum Discussion

deb_power123's avatar
deb_power123
Icon for Helper V rankHelper V
5 years ago

DAX query to Filter two duplicate columns independently using Measure table approach

Hi All,

 

I want to implement a feature like column filter in PowerBI matrix visualization. After checking lot of articles , I came to know that there is no direct visulization for column filter so let me explain my approach and scenario.I have a table say Sales with Quateryear, SalesAmount-A and SalesAmount-B as column names.I want to show the Sum of sales per Quateryear selection value in the filter. 

 

 

I created two tables with the values contained in filter as shown below.These tables are not related to the main table and are kept independent as I dont want one filter selection to affect the other one since both have the same field values and refer to duplicate columns.I want to implement this using parameter approach.If anyone has any better approach please let me know :-

Present visualization:-

 

Input source [excel]

QuaterYearSalesAmount-ASalesAmount-BPlace
Q1201710001000London
Q1201711001100London
Q1201711201120London
Q12017900900Tokyo
Q2201722002200Tokyo
Q2201721002100Tokyo
Q2201710001000Tokyo
Q22017500500Tokyo
Q2201719001900Tokyo
Q2201719001900Tokyo
Q2201720002000Tokyo
Q2201740004000Tokyo
Q2201721002100Tokyo
Q3201711001100Kaula Lampur
Q32017890890Kaula Lampur
Q4201722002200Berlin
Q4201723002300Berlin
Q4201729002900Berlin

 

I am using the functions for ParamTable1 and ParamTable2:

SelectedP1 = SELECTEDVALUE(ParamTable1[P1],1)
SelectedV1 = SELECTEDVALUE(ParamTable1[QY1])
SelectedP2 = SELECTEDVALUE(ParamTable1[P1],5)
SelectedV2 = SELECTEDVALUE(ParamTable2[QY2])
 
Expected output:
I want to select the values in the filter slicers and get the corresponding sum of sales.It should filter both SalesAmount-A and SalesAmount-B independent.I tried to handle this using the below DAX but its not working.
 
Total = IF(ParamTable1[SelectedP1] = 1 ,[TotalSalesA],
IF(ParamTable1[SelectedP1]= 2,[TotalSalesA],
IF(ParamTable1[SelectedP1]= 3,[TotalSalesA],0)))
 In above forumla
TotalSalesA = SUM(Sales[SalesAmount-A])
TotalSalesB = SUM(Sales[SalesAmount-B])
 
Any suggestions or help is highly appreciable.
 
Thanks
Sameer
Need Help

1 Reply

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    deb_power123 if I understood correctly, try thee two measures:

     

    Measure Sales A = 
CALCULATE ( [TotalSalesA], TREATAS ( VALUES ( ParamTable1[P1] ), Sales[QuarterYear] ) )

Measure Sales B = 
CALCULATE ( [TotalSalesB], TREATAS ( VALUES ( ParamTable2[P1] ), Sales[QuarterYear] ) )

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡