Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Measure not returning value at row level and returning only totals

Hello,

I am trying to build a visual that will give me No of Leads , Enquiries and Deals by Channel.

But it only returning total but nothing at row level for each channel. I need to find all the deals that have Lead Create Date and Sale Confirm Date from the Date Range I select from the Date slicer. 

 ( Say If I choose May 2024 ,  I need to get count of all leads created in May 2024   ,  and how many these leads converted into deals in May 2024).  For Deals , I need to look for Sale Confirm Date

Measure Definition

Deals = 
VAR MinDt =   MIN ( 'Date'[Date Key] )
VAR MaxDt =   MAX ( 'Date'[Date Key] )
RETURN
    CALCULATE (  SUM('Fact1'[KPI Deals]),
    FILTER(
KEEPFILTERS(VALUES('Fact1 Sales Funnel'[Lead Create Date Key])),
AND(
'Fact1'[Lead Create Date Key] >= MIN ( 'Date'[Date Key] ) ,
'Fact1'[Lead Create Date Key] <= MAX ( 'Date'[Date Key] )
)),
FILTER(
KEEPFILTERS(VALUES('Fact1'[Sale Confirmed Date Key])),
AND(
'Fact1'[Sale Confirm Date Key] >=  MIN ( 'Date'[Date Key] ) ,
'Fact1'[Sale Confirm Date Key] <= MAX ( 'Date'[Date Key] )
)))

Model View:

Active Relationship : Fact1.Lead Create Date Key --- Date.Date Key

InActive Relationship : Fact1.Enquiry Create Date  -- Date.Date Key , Fact1.Sale Confirm Date -- Date.Date Key

 

I need experts help to figure out what I am missing here and what can be done to fix this.

I have multiple dimension tables those are linked to both these fact table and need to create several views using dimensions from different tables.

 

Regards

Vineeta

 

Need Help