Duplicate columns when using a custom column table

[RevDan123]

Hi,

I am trying to create a matrix that uses a data table for the Row Names, and a data table for the Column Names so I can drive a button based on the cell chosen. The issue I am running in to is that the matrix is getting multiples of the same columns adn I have no idea how to get rid of it. 

Any help would be greatly appreciated. 

Thanks,

Dan

Current Month =

SWITCH (

    TRUE(),

    SELECTEDVALUE('ReferralRows'[RowName]) = "Referrals" && SELECTEDVALUE('ReferralColumns'[ColumnName]) = "CurrentMonth",CALCULATE(COUNT('ADF FactOpportunity'[FactOpportunityID])),

    SELECTEDVALUE('ReferralRows'[RowName]) = "Not Taken Up" && SELECTEDVALUE('ReferralColumns'[ColumnName]) = "CurrentMonth",CALCULATE (COUNT('ADF FactOpportunity'[FactOpportunityID]),USERELATIONSHIP ('ADF FactOpportunity'[LostDate], 'ADF DimDate'[DimDateID])),

    SELECTEDVALUE('ReferralRows'[RowName]) = "Pipeline" && SELECTEDVALUE('ReferralColumns'[ColumnName]) = "CurrentMonth",CALCULATE(COUNT('ADF FactOpportunity'[FactOpportunityID]),'ADF FactOpportunity'[FactFindCompletedDate] <> BLANK(), 'ADF FactOpportunity'[LostDate] = BLANK(), 'ADF FactOpportunity'[FirstExchangedDate] = BLANK()),

    SELECTEDVALUE('ReferralRows'[RowName]) = "FactFinds Completed" && SELECTEDVALUE('ReferralColumns'[ColumnName]) = "CurrentMonth",CALCULATE (COUNT('ADF FactOpportunity'[FactOpportunityID]),USERELATIONSHIP ('ADF FactOpportunity'[FactFindCompletedDate], 'ADF DimDate'[DimDateID])),

    SELECTEDVALUE('ReferralRows'[RowName]) = "On Risk/Sales Completed" && SELECTEDVALUE('ReferralColumns'[ColumnName]) = "CurrentMonth",CALCULATE (COUNT('ADF FactOpportunity'[FactOpportunityID]),USERELATIONSHIP ('ADF FactOpportunity'[FirstExchangedDate], 'ADF DimDate'[DimDateID]))

)

YTD =

SWITCH (

    TRUE(),

    SELECTEDVALUE('ReferralRows'[RowName]) = "Referrals" && SELECTEDVALUE('ReferralColumns'[ColumnName]) = "YTD",TOTALYTD(COUNT('ADF FactOpportunity'[FactOpportunityID]),'ADF DimDate'[Date]),

    SELECTEDVALUE('ReferralRows'[RowName]) = "Not Taken Up" && SELECTEDVALUE('ReferralColumns'[ColumnName]) = "YTD",CALCULATE (TOTALYTD(COUNT('ADF FactOpportunity'[FactOpportunityID]),'ADF DimDate'[Date]),USERELATIONSHIP ('ADF FactOpportunity'[LostDate], 'ADF DimDate'[DimDateID])),

    SELECTEDVALUE('ReferralRows'[RowName]) = "Pipeline" && SELECTEDVALUE('ReferralColumns'[ColumnName]) = "YTD",CALCULATE (TOTALYTD(COUNT('ADF FactOpportunity'[FactOpportunityID]),'ADF DimDate'[Date]),'ADF FactOpportunity'[FactFindCompletedDate] <> BLANK(), 'ADF FactOpportunity'[LostDate] = BLANK(), 'ADF FactOpportunity'[FirstExchangedDate] = BLANK()),

    SELECTEDVALUE('ReferralRows'[RowName]) = "FactFinds Completed" && SELECTEDVALUE('ReferralColumns'[ColumnName]) = "YTD",CALCULATE (TOTALYTD(COUNT('ADF FactOpportunity'[FactOpportunityID]),'ADF DimDate'[Date]),USERELATIONSHIP ('ADF FactOpportunity'[FactFindCompletedDate], 'ADF DimDate'[DimDateID])),

    SELECTEDVALUE('ReferralRows'[RowName]) = "On Risk/Sales Completed" && SELECTEDVALUE('ReferralColumns'[ColumnName]) = "YTD",CALCULATE (TOTALYTD(COUNT('ADF FactOpportunity'[FactOpportunityID]),'ADF DimDate'[Date]),USERELATIONSHIP ('ADF FactOpportunity'[FirstExchangedDate], 'ADF DimDate'[DimDateID]))

)

[RevDan123 · Accepted solution]

The thing causing my duplication is the multiple measures. I have now combined it all into one measure and it works as I want, albiet with a very long and confusing measure! Thanks for looking.

[Anonymous]

Hi @RevDan123 ,

Duplicates in the data model may behave as duplicates in the matrix. Check that there are no duplicate columns in the data model.

Can you provide more details about the ReferralRows and ReferralColumns table structure?

You can put your files in a dropbox, onedrive or googledrive folder and share the link with the community. Please provide more details with your desired out put and pbix file without privacy information.

Please refer to the related links for details:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.