Forum Discussion
Matrix Visual & Product Line Attributes
Hi,
These measures work
S = SUM(Data[Sale Amount])Measure = if(HASONEVALUE(Data[Product Line on Item]),if(MAX(Data[Product Line on Item])="Industrial",CALCULATE([S],Data[Industrial Sale?]="Y",all(Data[Product Line on Item])),[S]),[S])Hope this helps.
Hi liz_rowden,
I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it. I am also including .pbix file for your better understanding, please have a look into it:
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Fabric Community Forum.Best regards,
Ganesh Singamshetty.
Thank you very much. I have tried that, but in the portion below. I get the following message:
"The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."
The Item # is in the Sales Order table and the Product Line attribute is in the Items table. Sorry I should have specified that earlier. I tried adding the Product line to my Sales Order table, but then get the following message:
"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
SUM ( 'Table'[Sale Amount] ),
'Table'[Product Line on Item] = CurrentLine
|| 'Table'[Industrial Sale?] = "Y"
You could using FILTER, e.g.
Total Sales =
IF (
ISINSCOPE ( 'Product Lines'[Product Line on Item] ),
VAR CurrentLine =
SELECTEDVALUE ( 'Product Lines'[Product Line on Item] )
RETURN
IF (
CurrentLine = "INDUSTRIAL",
CALCULATE (
SUM ( 'Table'[Sale Amount] ),
FILTER (
Sales,
'Items'[Product Line on Item] = CurrentLine
|| 'Sales'[Industrial Sale?] = "Y"
)
),
CALCULATE (
SUM ( 'Table'[Sale Amount] ),
'Table'[Product Line on Item] = CurrentLine
)
),
SUM ( 'Table'[Sale Amount] )
)
Your Items table will belong to the expanded table of sales, so you should be able to place a filter on both columns.
Many thanks. This change did work to give me the correct totals in each of the Product Lines. However, I'm unable to get the drill-through to a sub-report to work. Is there a way to get that to work?
You could add a new column [Is Really Industrial] which is true if either the current Industrial Sale flag is set or the product line item is Industrial. You could do this using DAX, Power Query or SQL if your data source is a DB.
This would simplify the filter required, you wouldn't need an OR condition, you could just use the new column, and by including the new column in the drill through filters that should work as you want, I think.
I already have a column for [Is Really Industrial] on the Sales Order Lines using a calculated column in DAX. How are you suggesting I use that column differently from the other suggestions?