Filter two tables and get the result

DAX DAX Calculations
1

HI

I am trying to Filter a fact table based on column values in the filter table and also in the fact table.

Basically from PBIX I want to recreate that stacked column graph visual that I have created using drop-down filters but without those drop downs so a permanent graph.

The result I want is a table that shows me all the results for ‘Operation Short Text’[Power BI Details] = “Final Bag”

and filter only results in the fact table that meet the ‘SQ00 Confirmations’[Project Number] = “601” && “602” && “603”

Thanks

Dan

Total Pilatus Final Bags Completed = 
CALCULATE( [Total Confirmations],
    FILTER('Operation Short Text',
    'Operation Short Text'[Power BI Details] = "Final Bag" &&
    FILTER('SQ00 Confirmations',
    'SQ00 Confirmations'[Project Number] = "601" && "602" && "603"
)))

Pilatus CCA.pbix (4.1 MB)

2

Thought i just solved it but i haven’t

It locks in the Final bag operations but when I change the drop down filter for project numbers it still filters the chart visual I want static

Total Pilatus Final Bags Completed = 
CALCULATE( [Total Confirmations],
    FILTER('Operation Short Text',
    'Operation Short Text'[Power BI Details] = "Final Bag"),
    FILTER('SQ00 Confirmations',
    'SQ00 Confirmations'[Project Number] = "601" || "602" || "603")
)
3

Hello @Krays23,

Thank You for posting your query onto the Forum.

Well, the reason why it was still filtering the results is because slicer is created out of a Dimension Table i.e., “Project Numbers” and within the measure you’re referencing the Fact Table Column i.e., “SQ00 Confirmations[Project Numbers]” and therefore although the Project Numbers were hard-coded i.e., “601, 602 and 603” still it used to filter out the results. Just change the reference within the measure to the Dimension Table. Below is the measure alongwith the screenshot of the final results provided for the reference -

Total Pilatus Final Bags Completed = 
CALCULATE( [Total Confirmations] ,
    'Operation Short Text'[Power BI Details] = "Final Bag" ,
    'Project Numbers'[Project Number] IN { "601" , "602" , "603" } )

Final Results
Final Results1639×860 128 KB

Now, in the above screenshot, if you observe, irrespective of the slicer selection it’s giving the static results where Project Numbers equals “601, 602 and 603” and Power BI Details equals “Final Bag”.

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Pilatus CCA - Harsh.pbix (4.1 MB)

1 Like
4

Thank you Sir, Great explanation and makes it very clear many thanks.

Never seen the DAX written with the IN { before whats the benefit of that rather than = 601 || 602 || 603

'Project Numbers'[Project Number] IN { "601" , "602" , "603" } )