Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
I have a report where 1 measure gives me too long repsons time and I can not figure out who to do this better.
(result is correct)
I have 4 files CAP (fact) and SALES (fact) + date table + CUSTOMER table
Relationships: :
CAP to date (many to one)
SALES to date (many to one)
CAP to CUSTOMER (many to one)
SALES to CUSTOMER (many to one)
6 measures:
NS : CALCULATE(SUM(SALES[netsales]))
Sales FY22 = CALCULATE([NS], FILTER(ALL('DATE'),'DATE'[Fiscal Year] = 2022))
SALES YTD = TOTALYTD(CALCULATE([NS]),'DATE'[DATE],"6-30")
#CAP = CALCULATE(DISTINCTCOUNT('CAP'[CustomerID]),'CAP[ACTIVTY]>0)
#CAP active 2Y= calculate([#CAP], filter('CUSTOMER',[sales FY22]>1||[SALES YTD]>1))
Below last measure is the problem: (I want to find the YTD sales comming from "customers with CAP Active 2Y" )
Sales YTD from “#CAP Active 2Y” = calculate([sales YTD],FILTER('CUSTOMER',[ #CAP active 2Y]=1))
Anyone who has an idea how to do this better?
Thanks
Solved! Go to Solution.
@elseoutzen , Try like
SUmx(filter('CUSTOMER',[sales FY22]>1||[SALES YTD]>1),[#CAP])
or
SUmx(filter(values('CUSTOMER'[Customer]) ,[sales FY22]>1||[SALES YTD]>1),[#CAP])
@elseoutzen , Try like
SUmx(filter('CUSTOMER',[sales FY22]>1||[SALES YTD]>1),[#CAP])
or
SUmx(filter(values('CUSTOMER'[Customer]) ,[sales FY22]>1||[SALES YTD]>1),[#CAP])
This works perfect, Thank you so much 🙂 very helpful
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
107 | |
68 | |
48 | |
44 | |
43 |