topic Re: Tableau Function in Power BI in DAX Commands and Tips

Tableau Function in Power BI

Anonymous — Sat, 11 Jan 2020 03:59:46 GMT

I've recently been tasked with moving some of my Tableau Dashboards over to Power BI and like a lot of the users on here, I'm struggling getting some stuff involving LOD's to function in DAX. It's pretty frustrating. Here's my current issue that I've been stuck on for way to long;

In Tableau: I have an LOD function; { FIXED [Facility]: COUNTD(if [Source_System]='Online' then [Id] end)}

it just counts the number of distinct ids for each facility where the user purchased online. I then take this measure and add it to the filters card and set it to 'where > 10'. This way I can filter out any facility that has less than 10 online orders

In PowerBI: I thought I got the calculation right;

Test2 = CALCULATE(DISTINCTCOUNT(Query1[Id]),Query1[Source_System] = "Online",ALLEXCEPT(Query1,Query1[Facility]))

I then put the measure in as a filter and set it to > 10 and it doesn't do anything. Does anyone have any ideas?

Some stuff between the 2 programs translates super easily and some makes me want to pound my head against the wall.

Any help is much appreciated!

Thanks,

Micah

Re: Tableau Function in Power BI

Jimmy801 — Sat, 11 Jan 2020 17:00:50 GMT

Hello Anonymous

this measure won't work as a filter as it returns always a distinct count meaning a scalar value. And single scalar value can't not filtered. So you have to apply this formula as a new column in your data table and then using this new column as filter.

Hope this helps

Jimmy

Re: Tableau Function in Power BI

JarroVGIT — Sat, 11 Jan 2020 17:27:06 GMT

Hi Anonymous ,

My recommendation is to not punch your head against the wall 😉

In PowerBI, you can filter on Measures only in a Visual filter. To achieve what you want, you could create a Calculated Table with a summarize function to get a two column table with Facilities and their distinctcounts of Orders. You can then use that to put a filter on the Page or even Report level.

However, on a single visual you can filter on a measure. For example; I have a table visual with all categories of products and I want to filter out all categories where the distinct amount of corresponding products is bigger than 3. My measure is:

DistinctCount of Products in subcategory = DISTINCTCOUNT(DimProduct[ProductKey])

My table visual looks like this:

By dragging the measure to the filterpane and select "greater then 3", it will filter out all categories with 3 or less products. (in the following table visual, I have removed the measure as a column to demonstrate that it doesn't need to be visible in the Table visual to be filtered on):

Note that in PowerBI, a Slicer is a filter like any other filter. If you report contains a slicer on Facility you could add the measure to the Slicer Visual filter to not show any options where count<10. If you need the page or report filter, you will need to create a calculated table with distinct facilities and their corresponding IDs, create 1*many relationships from the calculated table to your fact/dim table and use that as a filter in the filter pane. In my example, the code would be something like this:

SubCategoriesWithDistinctCountsOfProducts = SUMMARIZECOLUMNS(DimProduct[ProductSubcategoryKey], "CountOfProducts", DISTINCTCOUNT(DimProduct[ProductKey]))

Let me know if this helps you out 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Re: Tableau Function in Power BI

Anonymous — Mon, 13 Jan 2020 17:02:28 GMT

Thank you Jimmy! this worked and glad to know I was at least on the right track.

I really appreciate your help with this!

-Micah

Re: Tableau Function in Power BI

Anonymous — Mon, 13 Jan 2020 17:04:55 GMT

Thank you for the detailed response. I'm going to give this a try this evening and see if it produces the desired affect.

-Micah

Re: Tableau Function in Power BI

JarroVGIT — Mon, 13 Jan 2020 18:42:39 GMT

No Problem, just don't forget to mark it as a solution if it does fit your needs (more than 1 response can be your solution) for others with similar questions to find the answer quickly.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂