Adding a filter to a table (in DAX)

[texmexdragon2]

I have this table below which computes Mode.  

I have a calculated column that is essentially a true/false column.   I want to compute the mode for each of those choices (the true ones, and the false ones), but based on the measure below.  Just adding that column as a slicer or filter on this measure does not work.  

Any ideas?

Mode_ShippedQty =

TOPN (

1,

ADDCOLUMNS (

VALUES ( ShippedOrders2022_Query[ShippedQty] ),

"Frequency", CALCULATE (COUNT ( ShippedOrders2022_Query[ShippedQty] ) )

),

[Frequency], 0

)

[v-yanjiang-msft · Accepted solution]

Hi @texmexdragon2 ,

Your measure mainly used a TOPN function, which will return a row in your formula instead of a value.

I create a sample. Single or Bulk is a calculated column according to your description.

In my understanding, you want to use Single or Bulk as a slicer and to calculate the Calculate the ShippedQty with the largest number of rows separately. Modify your formula to:

Mode_ShippedQty =
VAR _T =
    TOPN (
        1,
        ADDCOLUMNS (
            VALUES ( ShippedOrders2022_Query[ShippedQty] ),
            "Frequency", CALCULATE ( COUNT ( ShippedOrders2022_Query[ShippedQty] ) )
        ),
        [Frequency], 0
    )
RETURN
    MAXX ( _T, [ShippedQty] )

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[tamerj1]

Hi @texmexdragon2 

please try

Mode_ShippedQty =

TOPN (

1,

ADDCOLUMNS (

ALLSELECTED ( ShippedOrders2022_Query[ShippedQty] ),

"Frequency", CALCULATE (COUNT ( ShippedOrders2022_Query[ShippedQty] ) )

),

[Frequency], 0

)

[texmexdragon2]

@tamerj1  @Ashish_Mathur   Below is the calculated column I mentioned.    

My desire is to be able to have the mode calculated separately for Bulk  or  for Single.   

So if there are 30 rows.   I want to determine what the mode is for all of the rows that are bulk, and all of the rows that are single.    So if there was 10 rows for single, and the shipped quantity of "7" occurs on 6 out of 10 rows, then the mode would be 7.  

Single or Bulk =

SWITCH (

TRUE (),

CONTAINSSTRINGEXACT ( ShippedOrders2022_Query[Description], "40pk" ), "Bulk",

CONTAINSSTRINGEXACT ( ShippedOrders2022_Query[Description], "96PK" ), "Bulk",

"Single"

)

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.