topic Only show Sales Orders that only contains items of a specific type in DAX Commands and Tips

Only show Sales Orders that only contains items of a specific type

Hedan — Thu, 23 Nov 2023 13:13:54 GMT

Hello,
I have a table containing data of this structure:

I have put these three columns in a table visual. I want to create a slicer called "Only service items" that contains value TRUE/FALSE. If TRUE is picked in the slicer then I would like the visual to only show SalesID's that only contains rows of ItemType = Service.

Expected result:

Would anyone be so kind as to point me in the right direction.

Thanks!

Re: Only show Sales Orders that only contains items of a specific type

FreemanZ — Thu, 23 Nov 2023 14:26:31 GMT

hi Hedan ,

not sure if i fully get you, try to

1) plot a slicer with a calculated table like:

Slicer = SELECTCOLUMNS( {TRUE, FALSE}, "OnlyServiceItem", [Value] )

2) plot a table visual with all three columns and pull a measure like below to the filter pane of the visual and choose 1:

measure = VAR _selection = SELECTEDVALUE(Slicer[OnlyServiceItem]) VAR _salesid = MAX(data[salesid]) VAR _list = CALCULATETABLE( VALUES(data[itemtype]), ALL(data), data[salesid] = _salesid ) VAR _result = IF( _selection=FALSE(), 1, IF( _selection=TRUE && "Service" IN _list && COUNTROWS(_list)=1, 1,0 ) ) RETURN _result

it worked like:

Re: Only show Sales Orders that only contains items of a specific type

Hedan — Fri, 01 Dec 2023 11:07:26 GMT

This worked, thank you!