FORMAT - Switch and IF

[Anonymous]

Hi All,

I need to change my Switch calculation to remove the BLANK()

Var =

IF (

    HASONEVALUE (Swap[Measure]),

    SWITCH (

        VALUES (Swap[Measure]),

        "Sales",FORMAT('Sales'[Number],"#,##"),

        "Qty",FORMAT(SUM('Sales'[Qty]),"€#,##0.##")

        ),

     FORMAT(SUM('Sales'[Qty],"#,##")

)

The scenario I am working on is similar to the below post :

https://community.fabric.microsoft.com/t5/Desktop/FORMAT-ignores-filter/td-p/1161371

Can you please help how to remove the null values but also take care of the formatting?

I just need to update the above calculation to remove the NULL values which show up when I use the FORMAT within the calculation. Thanks

[Anonymous]

@lbendlin Can you please advise? Thanks

[lbendlin]

Something seems off with your formatting.  Having not seen your sample data here is my guess.

Var =

IF (

    HASONEVALUE (Swap[Measure]),

    SWITCH (

        VALUES (Swap[Measure]),

        "Sales",FORMAT('Sales'[Number],"€#,##0.##"),

        FORMAT(SUM('Sales'[Qty]),"#,##")

        ),

     FORMAT(SUM('Sales'[Qty],"#,##")

)

[Anonymous]

@lbendlin Thanks. The problem is not with formatting but with blanks. Please advise

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

@lbendlin Can you please help with above information? I tried creating a sample but it worked fine in it. Also do not see an optio to attach a pbix file in this post

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

@lbendlin 

Documents

Here is a link with sample data and pbix file. As informed, I want to filter on the Date filter and that is not working when I use the format calculation. Thanks

[lbendlin]

[Anonymous]

@lbendlin Thank you, this works for one column. But I have 3 columns with different measures. How can I get the same parameter to swap on different fields? So ideally I would like one parameter.

Documents

Thanks

Please refer to updated data here

But 3 columns in all will have 6 measures to swap and for every column it will be Qty, Sls but different fields. Thanks

[lbendlin]

You can use field parameters for both columns and measures.  

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Anonymous]

@lbendlin Thanks. I have uploaded the sample data with the new columns in the link above

I want the same parameter 

But I want to have 3 columns as output in the visual

Year-Month	Para	Para A	Para B	Para C

How can I use the parameter to swap values for all the 3 columns? Thanks

[lbendlin]

looks like the link still has the old file. Please validate.

[Anonymous]

@lbendlin Thanks. I checked again and it has the new excel file.

Please advise.