Filter data

Anonymous — Tue, 14 Apr 2020 11:50:24 GMT

I have to migrate report from SSRS to Power BI.

The report SSRS based on multidimensionnal cube. The report PowerBI based on SSAS Tabular. I use direct query when connecting to SSAS tabular.

I have a dataset on SSRS that I need to migrate

with

member UN as [Date Creation].[Année].currentmember.uniquename

member Lib as [Date Creation].[Année].currentmember.name

select

{UN, Lib} on 0

, filter( filter([Date Creation].[Année].[Année].members , [Date Creation].[Année].currentmember.name > "2016")

, (not isempty([Measures].[Nombre de h]) or not isempty([Measures].[Nombre de V]) )

) on 1

from mycube

How to migrate it?

Re: Filter data

technolog — Sun, 03 Sep 2023 17:28:05 GMT

The MDX query you provided is selecting two calculated members, UN and Lib, which represent the unique name and the name of the current year member from the [Date Creation].[Année] hierarchy. Then, it's filtering the years to only include those after 2016 and where either of the measures [Measures].[Nombre de h] or [Measures].[Nombre de V] are not empty.

To migrate this to DAX in Power BI, you'd do something like the following:

First, you'd need to ensure that your SSAS Tabular model has the equivalent tables and columns that represent the [Date Creation].[Année] hierarchy and the two measures.

Once you've confirmed that, you can start building your DAX query. You'd likely use the FILTER and CALCULATE functions to achieve the same result.

Here's a rough translation of your MDX query to DAX:

EVALUATE
VAR CurrentYear = YEAR(TODAY())

RETURN
FILTER(
ADDCOLUMNS(
VALUES('Date Creation'[Année]),
"UN", 'Date Creation'[Année],
"Lib", 'Date Creation'[Année]
),
'Date Creation'[Année] > 2016
&& (NOT(ISBLANK([Nombre de h])) || NOT(ISBLANK([Nombre de V])))
)
This DAX query starts by evaluating the current year. It then filters the [Année] column from the Date Creation table to only include years after 2016 and where either of the measures [Nombre de h] or [Nombre de V] are not blank. The ADDCOLUMNS function is used to add the UN and Lib columns to the result.

You'd then use this DAX query in Power BI to create a table or visual that represents the same data as your original SSRS report. Remember, the exact names of tables, columns, and measures in your SSAS Tabular model might differ from my example, so you'd need to adjust the DAX query accordingly.

topic Re: Filter data in DAX Commands and Tips

Filter data

Re: Filter data