Filter IF...

Question

Hello!I have this dax code,&nbsp;I need the meassure to Sum all the SO Net ( [SO Net] is a meassure = Calculate(Sum(POS DB'[SO Net)) ) of all the rows that say "Stock" ( 'POS DB'[Stock] ) but if it says "No Stock" only Sum [SO Net] &lt;= 25000So Only sum [SO Net] &lt;=25000&nbsp; if it says No Stock, otherwise Sum even if&nbsp;[SO Net] is &gt; than 25000&nbsp;The meassure should be calculated a level by Year, Quarter,&nbsp; Product Group, End User Country, and finally End User (That is why I used the Summarize function in the code below.&nbsp;Can you help me please??!!&nbsp;Meassure =SUMX&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'POS&nbsp;DB',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'POS&nbsp;DB'[Year],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'POS&nbsp;DB'[Quarter],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'POS&nbsp;DB'[Product&nbsp;Group],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'POS&nbsp;DB'[End&nbsp;User&nbsp;Country],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'POS&nbsp;DB'[End&nbsp;User&nbsp;Name]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[SO&nbsp;Net]&nbsp;&lt;=&nbsp;25000&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;[SO&nbsp;Net])

aj1973 · Answer

Hi&nbsp;Anonymous&nbsp;Your request is missing information!Can you share a sample Pbix?&nbsp;

selimovd · Answer

Hey&nbsp;Anonymous&nbsp;,&nbsp;how is the relationship between the tables 'DB' and 'POS DB'?If we should filter if&nbsp;'DB'[Stock] says "Stock" or "No Stock" we need to know how these tables are connected and also how the data looks like.&nbsp;Make it easy for us and we can help you.&nbsp;Best regardsDenis

mfelix · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
The question is that you are not adding the value on the table summarization so you are not filtering the values you need try the following code.
&nbsp;
Meassure =
SUMX (
    FILTER (
        SUMMARIZE (
            'POS DB',
            'POS DB'[Year],
            'POS DB'[Quarter],
            'POS DB'[Product Group],
            'POS DB'[End User Country],
            'POS DB'[End User Name],
            "@SONET", [SO Net]
        ),
        [@SONET] &lt;= 25000
    ),
    [@SONET]
)
&nbsp;
Be aware that I'm assuming that by the format [SO Net] is a measure in your model.
&nbsp;

anonymous · Answer

Hi! Sorry for my mistake, I just edited the post... Its all the same data base. Sorry!

anonymous · Answer

Mfelix thanks for you time and responseIndeed, [So Net] is a meassureHow can I Filter IF??I need the meassure to Sum all the SO Net ([SO Net]) of all the rows that say "Stock" ( 'POS DB'[Stock] ) but if it says "No Stock" only Sum [SO Net] &lt;= 25000So Only sum&nbsp;[SO Net] if it says Stock, otherwise Sum even if it is &gt; than 25000&nbsp;

Forum Discussion

Filter IF...

6 Replies

Related content

If formula

IF Statement with Filter

Nested IF

Filter

IF, Multiple