Show a specific column data When no filter is selected

[Alde]

Hi everyone 

I'm using this DAX to show a column value from "FEB" with a measure, but I have many measures to adjust every month, instead I want to use a predefied measure "CurrentMonth" to use in diffent measures but it says too many arguments.

please help me through, many thanks

YTD_CBha_Variance =

IF

(

ISFILTERED('ORDER_TABLE'[MONTH])=TRUE(), FIRSTNONBLANK('InventoryMovement'[YTD-CBha Variance %] ,1),

CALCULATE(Sum('InventoryMovement'[YTD-CBha Variance %] ),'InventoryMovement'[Month]="FEB" )

)

[Anonymous · Accepted solution]

Hi @Alde , 

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a measure.

Measure = IF(ISFILTERED('Table'[month])=TRUE(),FIRSTNONBLANK('Table'[value%],1),CALCULATE(SUM('Table'[value%]),'Table'[month]=SELECTEDVALUE('Table (2)'[date])))

What cause your error?

The following comparisons are not supported:

1. 1. Comparing to a column to a measure. SalesHeader[TerritoryID] = [LargestTerritory]
   2. Comparing a column to a an aggregate value. SalesHeader[TerritoryID] = MAX(TerritoryID[TerritoryID]])
   3. Comparing a column to a What-If parameter. SalesHeader[TerritoryID] =TerritoryParameter[TerritoryParameter Value]

More details: DAX error: A function ‘XXXX’ has been used in a True/False expression that is used as a table filter... 

If I have misunderstood your meaning, please provide your pbi file without privacy information and your desired output.

Best Regards

Community Support Team _ Polly

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

[Alde]

Hi @Anonymous 

Thanks for the sample, it did work until 2nd level of nested DAX,

When I tried this one, It doesn't work anymore at 3rd level while I'm trying to create a clustered stacked bar, but it works if I just use a value "Value" instead of SELECTEDVALUE().

Do you have any idea? 🥹 

Thank youuuuuu

BUD_OB2 = 
SUMX('Col_Type',
VAR CurrentType = 'Col_Type'[Col_Type]
    Return if
        (CurrentType="OB YEAR BUDGET",
        if (ISFILTERED('ORDER_TABLE'[MONTH])=truE(), FIRSTNONBLANK('FCST_BUD'[OB (start of month) BUD FY 42],1),
        CALCULATE(Sum('FCST_BUD'[OB (start of month) bud FY 42]), 'FCST_BUD'[Month] = SELECTEDVALUE(Col_Type[CurrentMonth]))
            )
        )
    )
        

[Anonymous]

Hi @Alde ,

I cannot reproduce your issue. Could you please provide your pbix file without privacy information and desired output?  If impossible, colud you please provide enough sample data to reproduce the issue?

Best Regards

Community Support Team _ Polly

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

[Alde]

Hi @Anonymous 

Here's the PBI sample file and attached an image for my idea.

Please help, thank youuuuu <33

https://drive.google.com/file/d/128YEVdMEsiBYfMy-R6J4SowibiA-sXoC/view?usp=sharing

OBActual_OB = 
SUMX('Col_Type',
VAR CurrentType = 'Col_Type'[Col_Type]
    Return
            if
            (CurrentType="OB Actual",
            IF  
            (ISFILTERED('ORDER_TABLE'[MONTH])=False(),
            CALCULATE(Sum('obcb'[OB Actual]), 'obcb'[Month] = "mar"),
            FIRSTNONBLANK('obcb'[OB Actual],1)
            )
            )
        )

[Anonymous]

Hi @Alde ,

I don't have access so I can't get your pbix file.Could you please provide again or use another way?

Best Regards

Community Support Team _ Polly

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

[Alde]

@Anonymous 

Finally I got the solution, it didn't work because I put the CurrentMonth column in the same table of the month used to check against, now i created another table without making any relationship and use it in the conditional query. 

OBActual_OB = 
SUMX('Col_Type',
            if
            ('Col_Type'[Col_Type]="OB Actual",
            IF  
            (ISFILTERED('ORDER_TABLE'[MONTH])=True(),FIRSTNONBLANK('obcb'[OB Actual],1),
            CALCULATE(Sum('obcb'[OB Actual]),'obcb'[Month]= SELECTEDVALUE(lastdatamonth[LastMonth]))
            )
        ))

[Alde]

https://drive.google.com/file/d/128YEVdMEsiBYfMy-R6J4SowibiA-sXoC/view?usp=sharing

[Alde]

I'll open to public, please access again a few sec later.

[Alde]

I'd like to use data from 'Col-Type'[Current_Month) in stead of typing "Mar" in the measure, cuz there's be a few dozens of them to use, I can't update all of them every month. Thank youu.

[Alde]

Ahh, I also tried assigning a variable the value using SELECTEDVALUE() function and using it at third level, and changing back and forth of the nests, but still doesn't work unless it's a typed value,

[Ashish_Mathur]

Hi,

Share some data and show the expected result.