Power BI Report Builder / DAX-Studio dimension and fakt Filter not working as exptected

[Benedikt_Sch]

Hello, 

I am wondering why filtering in Dax no working as ecpected. Neither in Dax Studio nor in Power Bi Report Builder with genrated code by the wizard.

Datamodel:

I use a shared Power-Bi Dataset that is imported from SQL. The model is basically a star-Schema. Fact table in the middle aber dimensions like customer , company, sales or date.  Linked by ID and bidirectional. 

Dimension tables

• customer
• company
• sales
• date

The fact table

• sales quantity
• sales value
• cacaluated Measures  sales value in EUR 

Problem 1:  Filtering dimension by an other dimension

if I create a dataset with the wizard, where I only display dimension "sales- sales ID" and set a Filter/Parameter "customer -  name" to a specific customer (0011260), the salesid are not filtered by the specificy customer.

It shows all salesids.

Result in report builder:

Code:

EVALUATE SUMMARIZECOLUMNS('Umsatz Dim Auftrag'[salesid], RSCustomDaxFilter(@UmsatzDimKundeKundennummer,EqualToCondition,[Umsatz Dim Kunde].[customer],String))

Expected Result:

Why filtering not working right? 

my workaround is to add a countrows fact table or any other calculated measure. But I think this cannot be a real solution.

Problem 2: Aggregation fact not calculated measures

if i create a Dataset with the wizard  or event in Dax-Studio

• Dimension sales - salesid
• fact quantity
• count rows because of problem 1

filter: 1 salesid = 395A0000203

Result:

Code: 

/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
   'Umsatz Dim Auftrag'[salesid] ,
    'Umsatz - Fakten'[qty],
    KEEPFILTERS( TREATAS( {"395A0000203"}, 'Umsatz Dim Auftrag'[salesid] ))
     , "CountRows-Fact", CALCULATE(COUNTROWS('Umsatz - Fakten'))
)
/* END QUERY BUILDER */

Expected result:

Code:

EVALUATE
SUMMARIZECOLUMNS(
   'Umsatz Dim Auftrag'[salesid] ,

    KEEPFILTERS( TREATAS( {"395A0000203"}, 'Umsatz Dim Auftrag'[salesid] ))

     , "SUM_QTY",     sum('Umsatz - Fakten'[qty])
      , "CountRows-Fact", CALCULATE(COUNTROWS('Umsatz - Fakten'))
)

Why do i have to specify the aggregation again in the code? I specified my fact - qty already in Power Bi as summarize by Sum.

When i create the fact as measure it surely works. But for this i have to create a simple sum measure for all my measures.

greetings

Benedikt

[d_gosbell · Accepted solution]

---

@Benedikt_Sch wrote:

my workaround is to add a countrows fact table or any other calculated measure. But I think this cannot be a real solution.

It's a bit hard to say without knowing what the relationships look like in your model. But typically dimension tables are not directly related to each other hence they do not filter each other. Typically they are related through a fact table and including something like a countrows measure in the query is the best way of filtering one dimension against another.

---

@Benedikt_Sch wrote:

Why do i have to specify the aggregation again in the code? I specified my fact - qty already in Power Bi as summarize by Sum.

The default behaviour when including a column in a query is to do a "group by". The "summarize by" property is simply extra metadata that Power BI Desktop uses to build a measure on the fly by injecting the appropriate aggregate function into the query (as you have done in the second query). It's possible that I could mimic this behaviour in DAX Studio, but I have no idea if the Report Builder team has any plans to do anything like this.

---

@Benedikt_Sch wrote:

When i create the fact as measure it surely works. But for this i have to create a simple sum measure for all my measures.

---

Creating explicit measures like this is actually a best practice as it allows other client tools (like Excel, Report Builder and DAX Studio) to work more easily with your data model.

[Benedikt_Sch]

thank you for the answer!

That is not the answer i was looking for, but the answer I was afraid of 😊

I came from SSRS and MDX. Paginated Reports with DAX is completely crazy in some ways...

[d_gosbell]

---

@Benedikt_Sch wrote:

I came from SSRS and MDX. 

---

So MDX is the same as DAX in regards to your first question. If you just put two dimension attributes in a query you would get a crossjoin of all the values if you did not include a measure.

But the measure configuration in DAX models is different to MDX. All measures are defined as calculation expressions in DAX. The "summarize by" property is just a hint to the client tools, it does not actually create a measure in the model like it did for MDX based models.