Forum Discussion

RevDan123's avatar
RevDan123
Frequent Visitor
2 years ago
Solved

Duplicate columns when using a custom column table

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's avatar
    RevDan123
    2 years ago

    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.

2 Replies

  • RevDan123's avatar
    RevDan123
    Frequent Visitor
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.