Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I need a solution for a DAX measurement based on this SQL:
I need to know how I can sort out how many V_KUNDELISTE_AKTIV_GES.kundenr that have V_KUNDELISTE_AKTIV_GES.NAERINGSKODENAMN = 'Hytter og fritidshus' and not
V_KUNDELISTE_AKTIV_GES.NAERINGSKODENAMN='Husholdninger'
SELECT V_KUNDELISTE_AKTIV_GES.FRA,
V_KUNDELISTE_AKTIV_GES.STARTER,
V_KUNDELISTE_AKTIV_GES.MAALEPKTNR,
V_KUNDELISTE_AKTIV_GES.TIL,
V_KUNDELISTE_AKTIV_GES.KUNDENAVN,
V_KUNDELISTE_AKTIV_GES.KRAFTLEV,
V_KUNDELISTE_AKTIV_GES.VALSTATUS,
V_KUNDELISTE_AKTIV_GES.KUNDENR,
V_KUNDELISTE_AKTIV_GES.KONTRAKTNR,
V_KUNDELISTE_AKTIV_GES.POSTNR,
V_KUNDELISTE_AKTIV_GES.MAALEPUNKTADRESSE,
V_KUNDELISTE_AKTIV_GES.POSTSTED_ANLEGG,
V_KUNDELISTE_AKTIV_GES.KOMMUNE,
V_KUNDELISTE_AKTIV_GES.FOODSELDATO,
V_KUNDELISTE_AKTIV_GES.FORETAKSNR,
V_KUNDELISTE_AKTIV_GES.EPOST,
V_KUNDELISTE_AKTIV_GES.MOBILTLFNR,
V_KUNDELISTE_AKTIV_GES.TELEFONNR,
V_KUNDELISTE_AKTIV_GES.KOMPKODESM,
V_KUNDELISTE_AKTIV_GES.MAALEPUNKT,
V_KUNDELISTE_AKTIV_GES.NETTEIER,
V_KUNDELISTE_AKTIV_GES.AARSFORBRUK,
V_KUNDELISTE_AKTIV_GES.RESEPTNR,
V_KUNDELISTE_AKTIV_GES.RESEPTID,
V_KUNDELISTE_AKTIV_GES.RESEPTNAMN,
V_KUNDELISTE_AKTIV_GES.NAERINGSKODENAMN,
V_KUNDELISTE_AKTIV_GES.METODE,
V_KUNDELISTE_AKTIV_GES.EKSTRA1,
V_KUNDELISTE_AKTIV_GES.BETTYPENAVN
FROM BRUKARDATA.V_KUNDELISTE_AKTIV_GES
WHERE (V_KUNDELISTE_AKTIV_GES.NAERINGSKODENAMN = 'Hytter og fritidshus')
and not exists(select * from brukardata. V_KUNDELISTE_AKTIV_GES b
where b.kundenr= V_KUNDELISTE_AKTIV_GES.kundenr
and b.NAERINGSKODENAMN='Husholdninger')
Solved! Go to Solution.
Hi @BENGAB,
Maybe you can try to use below formula to get count of specific item.
Result= VAR temp = FILTER ( ALLSELECTED ( V_KUNDELISTE_AKTIV_GES ), [NAERINGSKODENAMN] IN { "Hytter og fritidshus", "Husholdninger" } ) RETURN COUNTAX ( FILTER ( temp, [KUNDENR] = MAX ( [KUNDENR] ) && [NAERINGSKODENAMN] <> "Husholdninger" ), [NAERINGSKODENAMN] )
Regards,
Xiaoxin Sheng
HI @BENGAB,
For 'exist' filter, you can try to use in keyword. if you want 'not exists' operation, you can combine use NOT function and in keyword.
Sample:
Result = IF ( NOT ( 'Table 1'[column] IN VALUES ( 'Table 2'[column] ) ), 'true expression', 'false expression' )
Reference link:
Notice: current in keyword not supported to compare multi-value with multi-value.
Regards,
Xiaoxin Sheng
Hi, Thanks for update.
What I realy need is a measure that counts "Hytterkunder" where "Hyttekunder" is not like "Husholdninger" for the same KUNDENR.
In the picture I use to measure:
Hyttekunder = CALCULATE(V_KUNDELISTE_AKTIV_GES[TOTALT_aktive];filter(V_KUNDELISTE_AKTIV_GES;[NAERINGSKODENAMN]="Hytter og fritidshus"))
Husholdningskunder = CALCULATE(V_KUNDELISTE_AKTIV_GES[TOTALT_aktive];filter(V_KUNDELISTE_AKTIV_GES;[NAERINGSKODENAMN]="Husholdninger"))
Hi @BENGAB,
Maybe you can try to use below formula to get count of specific item.
Result= VAR temp = FILTER ( ALLSELECTED ( V_KUNDELISTE_AKTIV_GES ), [NAERINGSKODENAMN] IN { "Hytter og fritidshus", "Husholdninger" } ) RETURN COUNTAX ( FILTER ( temp, [KUNDENR] = MAX ( [KUNDENR] ) && [NAERINGSKODENAMN] <> "Husholdninger" ), [NAERINGSKODENAMN] )
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |