Link Measures to a Column

[Anonymous]

Hello everyone,

I have 17 measures that are all stored in a Calendar Table. These measures are derived from multiple calculations coming from multiple tables as well.

I'm trying to link these measures to another table (Metrics) which looks like this. Basically, the measures should populate the right category according to the table below.

Due to no relationship between the measures in Calendar table and the Metrics table above, my matrix is showing duplications as per below.

I have tried to create a calculated column that switches between each measures and goes into the category itself. My intention is to link this column to another column called "Physical Flow" from the Metrics table. The calculated column is not working.

Column = SWITCH(

    TRUE,

            [Steris (<7days)] = [Steris (<7days)],"Sterilization" &&

            [Steris (>7days)] = [Steris (>7days)],"Sterilization" &&

            [Steris (China TJ)] = [Steris (China TJ)],"Sterilization" &&

            [In Qual. I  (<2days)] = [In Qual. I  (<2days)],"Quality" &&

            [Blocked (<5days)] = [Blocked (<5days)],"Quality" &&

            [In Qual. I  (>2days)] = [In Qual. I  (>2days)],"Quality" &&

            [Blocked (>5days)] = [Blocked (>5days)],"Quality" &&

            [NoPicklist] = [NoPicklist],"Inventory" &&

            [UnRestricted (All)] = [UnRestricted (All)],"Inventory" &&

            [UnRestricted (China TJ)] = [UnRestricted (China TJ)],"Inventory" &&

            [Pending PGI] = [Pending PGI],"Inventory" &&

            [Done PGI] = [Done PGI],"Inventory" &&

            [Target Shipment (Real Time)] = [Target Shipment (Real Time)],"Outbound Shipment" &&

            [Actual Shipment] = [Actual Shipment],"Outbound Shipment" &&

            [Balance] = [Balance],"Outbound Shipment" &&

            [Target Shipment (M-1)] = [Target Shipment (M-1)],"Outbound Shipment" &&

            [Real Time Target vs M-1 Target] = [Real Time Target vs M-1 Target],"Outbound Shipment"

)

Let me know if there's anything else needed from my end.

Thanks!

[danextian · Accepted solution]

Hi @Anonymous 

You will need to create a measure that switches according to the row  value from a disconnected table column

For example,

SWITCH (
    SELECTEDVALUE ( 'metrics'[metrics name] ),
    "Sales", [Sales Measure],
    "Volume", [Volume Measure],
    "ATV", DIVIDE ( [Sales Measure], [Volume Measure] )
)

Even if there isn't a relationship between metrics and other tables, the measures should still return the correct value as they're calculation is essentially being affected by the other tables.

[v-sgandrathi]

Hi @Anonymous,

May I ask if you have gotten this issue resolved?

If it is clear, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Best Regards,

Sahasra.

[v-sgandrathi]

Hi @Anonymous,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @Anonymous,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!

Regards,
Sahasra.

[v-sgandrathi · Accepted solution]

Hi @Anonymous,
Thank you for reaching out to the Microsoft Fabric Community forum.

As you mentioned, 17 measures are derived from multiple calculations and tried to create a calculated column that switches between each column which doesn’t work.

Yes, it doesn’t work because the issue here is that calculated columns cannot hold or switch between measures as measures are evaluated dynamically when you interact with a visual. By using calculated columns, it cannot hold multiple measure values. This is why the approach of creating a calculated column to link measures to the 'Physical Flow' column is not working.

Instead of using a calculated column, we can create a separate Mapping Table which link each measure to its respective category. This table will act as a bridge to organize the measures properly. After that we can build dynamic measures in Power BI that will show the correct values for each category without duplications. This method ensures accurate results in your reports.

If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more easily.

Best Regards,
Sahasra.

[danextian · Accepted solution]

Hi @Anonymous 

You will need to create a measure that switches according to the row  value from a disconnected table column

For example,

SWITCH (
    SELECTEDVALUE ( 'metrics'[metrics name] ),
    "Sales", [Sales Measure],
    "Volume", [Volume Measure],
    "ATV", DIVIDE ( [Sales Measure], [Volume Measure] )
)

Even if there isn't a relationship between metrics and other tables, the measures should still return the correct value as they're calculation is essentially being affected by the other tables.