Create dynamically calculated table based on measures to determine if FY Plan or Budget is defined

Hi Cherie,

Thanks for the quick feedback and the PBI model. Although the two measures calculate well, I can only use them as 'values' in the resulting matrix visual. But what I really need is to use the data in the matrix as 'filters'. So the users can select them in a slicer or set them as default page/report filter.

I already tried various hard coded variations of the result matrix too, but only your layout seems to really do the trick.

I also managed to create the M code to generate the result matrix as a query table in Power Query. So I can at least use the data for slicers and page/report filter as needed. I just had to ensure that when building the relation between the Project table and the Matrix Result set, the Cross Filtering is enabled in both directions.

This was the M code that generates the matrix as a new query table (called 'Dynamic FY relation 3' table in screenshot above).

let
    Source = #"Financial Facts",
    #"Merged Queries" = Table.NestedJoin(Source,{"Date"},Calendar,{"Date"},"Calendar",JoinKind.LeftOuter),
    #"Expanded Calendar" = Table.ExpandTableColumn(#"Merged Queries", "Calendar", {"FY"}, {"Calendar.FY"}),
    #"Grouped Rows" = Table.Group(#"Expanded Calendar", {"PID", "Source", "Calendar.FY"}, {{"FY Total", each List.Sum([Amount]), type number}}),
    #"Merged Columns" = Table.CombineColumns(#"Grouped Rows",{"Source", "Calendar.FY"},Combiner.CombineTextByDelimiter(" in ", QuoteStyle.None),"FY Relation"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[#"FY Relation"]), "FY Relation", "FY Total", List.NonNullCount),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Planned in FY18", type text}, {"Budget in FY18", type text}, {"Planned in FY19", type text}, {"Budget in FY19", type text}}),
    #"Replaced Value (yes)" = Table.ReplaceValue(#"Changed Type","1","yes",Replacer.ReplaceText,{"Planned in FY18", "Budget in FY18", "Planned in FY19", "Budget in FY19"}),
    #"Replaced Value (no)" = Table.ReplaceValue(#"Replaced Value (yes)","0","no",Replacer.ReplaceText,{"Planned in FY18", "Budget in FY18", "Planned in FY19", "Budget in FY19"})
in
    #"Replaced Value (no)"

I uploaded a revised PBI model to Dropbox in case someone may find this useful. I also includes the test with soem static result sets that did not support my requirements. (Revised PBI model)

What I still don't know, is if the same table can also be added directly in DAX, and if this would be more performant in DAX. But at least I can go on with what I have so far.

Regards,

Michael