Forum Discussion

Analitika's avatar
Analitika
Post Prodigy
5 years ago

Add custom field to selectedvalue column in matrix

I have matrix where are column from table1[types], which contains 3 types 1. fuel 2.diesel 3. Adblue, then i link this table to table2 which has two columns, 1. sum1 2. sum2   i want to show in mat...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Analitika,

You can create a parameter table with all category and custom category types and use this to replace raw fields.
Then you need to write a measure formula with switch function and selectedvalue to check current row content and return corresponding expression results.

Write Conditional Statement Using SWITCH in DAX and Power BI 

Regards,

Xiaoxin Sheng

Analitika's avatar
Analitika
Post Prodigy
5 years ago

Anonymous 

No i cant, as category type then not included in main table and not showing in result matrix

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI Analitika,

    How about manually create them? You can use datatable function to define them if they not too many records.
    BTW, these categories not required to store in the main table, they are interacting with DAX functions and not need to link to raw table.
    Regards,
    Xiaoxin Sheng

    • Analitika's avatar
      Analitika
      Post Prodigy
      5 years ago

      Anonymous  you can try to help me create 1 million rows

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        HI Analitika,

        Why do you mean to create 1 million rows? Can you please share some dummy data with your raw table structures?

        How to Get Your Question Answered Quickly 
        BTW, I think you only need the raw table fields and append the extra field. then you can use it as axis, and write a measure formula to replace the result of the new add field type to summary column 2.

        New Table =
UNION ( VALUES ( Table1[Type] ), ROW ( "Type", "Costs" ) )
        Measure =
VAR currType =
    SELECTEDVALUE ( 'New Table'[Type] )
RETURN
    IF (
        currType = "Costs",
        CALCULATE (
            SUM ( Table2[Sum2] ),
            FILTER ( ALLSELECTED ( Table1 ), [Type] = currType )
        ),
        CALCULATE (
            SUM ( Table2[Sum1] ),
            FILTER ( ALLSELECTED ( Table1 ), [Type] = currType )
        )
    )

        Regards,

        Xiaoxin Sheng