Tableau Fixed LOD to PBI Issue with Filtering

Question

Hello everyone,&nbsp;I'm struggling with converting this Tableau fixed LOD expression into PBI.&nbsp;Volume Industry = {fixed [Year], [Month], [Market], [Trade Channel], [ProductCategoryGroup]: SUM([Volume])}&nbsp;&nbsp;&nbsp;So far in PBI I have converted this Tableau measure as follows:Volume Industry =CALCULATE(SUMX('COT', 'COT'[Volume]),ALLEXCEPT('COT', 'COT'[Date].[Year],'COT'[Date].[Month],'COT'[Date].[Date], 'COT'[Year], 'COT'[Month], 'COT'[Market], 'COT'[Trade Channel],'COT'[Product Category Group]))&nbsp;&nbsp;&nbsp;My data is organized in the following way (simplified for this issue):DateMarketTrade ChannelLocationProduct GroupProductVolume2019-01JapanAirportsOsakaGroup 1Product 1.1102019-01JapanAirportsTokyoGroup 1Product 1.252019-01JapanFerriesOsakaGroup 1Product 1.242019-01JapanAirportsOsakaGroup 2Product 2.1202019-02JapanAirportsOsakaGroup 1Product 1.282019-02JapanAirportsTokyoGroup 1Product 1.212019-02JapanFerriesOsakaGroup 2Product 2.1102019-02JapanAirportsOsakaGroup 1Product 1.15&nbsp;What I would like to have as a behavior is that for any filtering or visual that is at a level higher than Date, Market, Trade Channel, Product group, the Industry volume is basically the sum of volume.However if we go at a lower level (example location), then the industry level will be equivalent to the industry level at Date, Market, Trade Channel, Product Group (related to the location).&nbsp;I have the following issues: when I have filters on either of the fields in the 'AllExcept' close, the formula works. However, if I am filtering on a lower granularity (example: Osaka), the volume industry is calculated across all markets, TC, Product Groups in my data set (even though Osaka belongs only to Japan).&nbsp;I tried to illustrate my issue below:&nbsp;I feel that I am missing something that allows the formula to take into account context (e.g. if a location selected belongs to a single market, I should have an implicit filter on Market).&nbsp;Thanks a lot for your help!Cheers&nbsp;

anonymous · Answer

HI&nbsp;dreyz64,
I'd like to suggest you use the following measure formula lif it suitable to your requirement:
&nbsp;
Volume Industry =
CALCULATE (
    SUM ( 'COT'[Volume] ),
    ALLSELECTED ( 'COT' ),
    VALUES ( 'COT'[Date] ),
    VALUES ( 'COT'[Market] ),
    VALUES ( 'COT'[Trade Channel] ),
    VALUES ( 'COT'[Product ),
    VALUES ( 'COT'[Product Category Group] )
)

&nbsp;
If above not help, please share some dummy data to test.
BTW, You can also take a look at below blog mentioned the difference between all functions:
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT&nbsp;
Regards,
Xiaoxin Sheng

anonymous · Answer

HI&nbsp;dreyz64,
In fact, my formula is configurable, I already listed the categories fields with 'values' function which used as the category group.So if you want aggregate records that ignore specific category fields, you only need to remove not needed category fields to ignore the calculate split on that category. (for your scenario, it should calculate based on date, market, product category group fields)
Modified formual:
Volume Industry =
CALCULATE (
    SUM ( 'COT'[Volume] ),
    ALLSELECTED ( 'COT' ),
    VALUES ( 'COT'[Date] ),
    VALUES ( 'COT'[Market] ),
    VALUES ( 'COT'[Product Category Group] )
)
In addition, I also add some comments on the old formula, you can check if it helps you to understand my formula.
Volume Industry =
CALCULATE (
    SUM ( 'COT'[Volume] ),
    //keep current filter effects and interaction on COT table
    ALLSELECTED ( 'COT' ),
    //add values functions to use current category field value as condition to prevent the calculation across different field values and achieve data group effects
    VALUES ( 'COT'[Date] ),
    VALUES ( 'COT'[Market] ),
    VALUES ( 'COT'[Trade Channel] ),
    VALUES ( 'COT'[Product ),
    VALUES ( 'COT'[Product Category Group] )
)
Regards,
Xiaoxin Sheng

amitchandak · Answer

dreyz64 ,
https://dataveld.com/2018/02/17/tableau-to-power-bi-fixed-lod-expressions/
https://dataveld.com/2018/03/10/tableau-power-bi-include-level-detail-expressions/
https://visualbi.com/blogs/microsoft/powerbi/implementing-lod-tableau-power-bi-qliksense/

dreyz64 · Answer

amitchandak&nbsp;I have already looked at these links and this is how I came up with my formula.I need specific help on my formula not generic answers that I have already checked and implemented....

dreyz64 · Answer

Anonymous&nbsp;Thank you for your answer.I had actually changed my formula to this and it had worked but your solution works as well so i will use yours 🙂&nbsp;_Volume Industry = 
    SUMX(SUMMARIZE('COT','COT'[Date].[Year],'COT'[Date].[Month],'COT'[Date].[Date], 'COT'[Year], 'COT'[Month], 'COT'[Market], 'COT'[Trade Channel],'COT'[Product Category Group]),
        CALCULATE(
            SUM('COT'[Volume]), 
            ALLEXCEPT('COT', 'COT'[Date].[Year],'COT'[Date].[Month],'COT'[Date].[Date], 'COT'[Year], 'COT'[Month], 'COT'[Market], 'COT'[Trade Channel],'COT'[Product Category Group])
        )
    )&nbsp;However I have a new issue now (with your formula and the one above): I want to calculate the industry volumes by Owner (to later calculate Share of Market). I tried to illustrate my issue in the image below:&nbsp;The first table is a simplified version of my raw data.The second table is the result when applying the Industry volume formula you gave me. It is correct.Where my issue is, is if I try to plot in a matrix the owner and calculate the industry volume. What PBI does is that it looks for each owner in which Market-Trade Channel- product group it belongs to and gives the industry volume based on this.However, I would like it to take the total of industry volume.So if owner 2 is only in airports, the industry volume should still be the sum of industry volumes in airports AND ferries, not simply ferries.&nbsp;Could you please help me?Thanks!

Forum Discussion

Tableau Fixed LOD to PBI Issue with Filtering

6 Replies

Related content

CALCULATE TOP 20% (Tableau Fixed Percentile to DAX)

Fixed Tableau function into Power BI DAX

Tableau's FIXED into DAX

Tableau vs Power BI AI Features

Actual quadruple how to fix?

Date	Market	Trade Channel	Location	Product Group	Product	Volume
2019-01	Japan	Airports	Osaka	Group 1	Product 1.1	10
2019-01	Japan	Airports	Tokyo	Group 1	Product 1.2	5
2019-01	Japan	Ferries	Osaka	Group 1	Product 1.2	4
2019-01	Japan	Airports	Osaka	Group 2	Product 2.1	20
2019-02	Japan	Airports	Osaka	Group 1	Product 1.2	8
2019-02	Japan	Airports	Tokyo	Group 1	Product 1.2	1
2019-02	Japan	Ferries	Osaka	Group 2	Product 2.1	10
2019-02	Japan	Airports	Osaka	Group 1	Product 1.1	5