Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PZUDGW
Helper I
Helper I

Help with Dax-measure

Dear,

 

When I use the DAX-quary as shown down below, I do not get the aspected result.

Aantal geannuleerde ontruimingen = 
-- aantal geannuleerde ontruimingen in de geselecteerde periode --
-- zijn wel gepland, maar er is een reden voor annulering opgegeven --
VAR vJaar = SELECTEDVALUE(_Calender[Year])
RETURN
    CALCULATE(
            DISTINCTCOUNT(factMRCUSTDEBTSETTLEMENT[DEBTSETTLEMENTID]),
            factMRCUSTDEBTSETTLEMENT[SGCUCDEBTSETTVERDCANCELREASON] <> BLANK(),
            YEAR(factMRCUSTDEBTSETTLEMENT[DATEEVACUATIONPLANNED])=vJaar,
            USERELATIONSHIP(_Calender[Date],factMRCUSTDEBTSETTLEMENT[DATEEVACUATIONPLANNED])
    )

Result:

PZUDGW_0-1648120644088.png

 

All the fields are in the same table (factMRCUSTDEBTSETTLEMENT). In this measure I need to remove all 

factMRCUSTDEBTSETTLEMENT[SGCUCDEBTSETTVERDCANCELREASON] that are blank. I probably need a different command. So I need some help 😉
 
Greez Api
6 REPLIES 6
tamerj1
Super User
Super User

HI @PZUDGW 
Is the reden van onnullering column in the same table?

Klopt (tabel factMRCUSTDEBTSETTLEMENT). Daarom vind ik het ook zo vreemd dat dan de weergave verstoord wordt. Wanneer ik de regel voor het verwijderen van de lege regels verwijder, worden de aantallen wel netjes per maand getoond. Daarom heb ik het vermoeden dat het in de regel voor de blanks zit..

@PZUDGW 
I understand that the fact table has two relationships with the Date table. One is active and the other is inactive. And you are using USERELATIONSHIP to activate the inactive one.
It happened with me once and I could not find the answer but you may also deactivate the active relationship using CROSFILTER and set the active relationship to "None". It worth nothing to try.

@tamerj1 

That's right. All the columns are in the same table. When I try to filter all blank Cancelationreasons, the PBI-table is not showing the data right (all reasons showing the same numbers). When I remove the command to filter the blanks, the PBI-table suddenly shows the right data (but including all blanks).

 

Using CROSSFILTER didn't do the trick..

Any other suggestions?

 

Greez Api

 

@PZUDGW 
Try this

Aantal geannuleerde ontruimingen =
-- aantal geannuleerde ontruimingen in de geselecteerde periode --
-- zijn wel gepland, maar er is een reden voor annulering opgegeven --
VAR vJaar =
    SELECTEDVALUE ( _Calender[Year] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( factMRCUSTDEBTSETTLEMENT[DEBTSETTLEMENTID] ),
        ALLNOBLANKROW ( factMRCUSTDEBTSETTLEMENT[SGCUCDEBTSETTVERDCANCELREASON] ),
        YEAR ( factMRCUSTDEBTSETTLEMENT[DATEEVACUATIONPLANNED] ) = vJaar,
        USERELATIONSHIP ( _Calender[Date], factMRCUSTDEBTSETTLEMENT[DATEEVACUATIONPLANNED] )
    )
Greg_Deckler
Super User
Super User

@PZUDGW Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors