How to reference a column from a virtual table to be used in a IF statement ?

Anonymous — Thu, 04 May 2023 12:13:50 GMT

Hello Power BI & DAX community,

I am trying to create a measure that calculates the number of new suppliers for each period based on if the suppliers registration date is within that period or not. For example if a supplier has Registration date 20220120 it should be counted as a new supplier for period 202201 etc.

This is what I have tried:

EVALUATE VAR cte_Periods = SUMMARIZECOLUMNS ( Dim_Time[Period], "Monthmin", DATE ( YEAR ( EOMONTH ( MAX (Dim_Time[Date]), 0 ) ), MONTH ( EOMONTH ( MAX (Dim_Time[Date]), 0 ) ), 1 ), "Monthmax", MAX (Dim_Time[Date] ) ) RETURN SUMX ( SUMMARIZECOLUMNS ( Purchase[Supplier number], "Reg date", MIN(Purchase[Registration date]) ), IF ( AND ( [Reg date] >= SELECTEDVALUE ( cte_Periods[Monthmin] ), [Reg date] <= SELECTEDVALUE ( cte_Periods[Monthmax] ) ), 1, BLANK () ) )

But complains that it cannot find table 'cte_Periods'. How can I apply the virtual cte_Periods table to be used in the return statement ?. I would assume that I need somekind of cross apply or cross join ?

I solved it like this in SQL:

WITH cte_Periods AS (SELECT TIM.Period, DATEFROMPARTS(YEAR(EOMONTH(MAX(TIM.Date))), MONTH(EOMONTH(MAX(TIM.Date))), 1) AS Monthmin, CONVERT(DATE, MAX(TIM.Date)) AS Monthmax FROM [dbo].[Dim_Time] AS TIM GROUP BY TIM.Period) SELECT COUNT(DISTINCT CASE WHEN CONVERT(DATE, CONVERT(VARCHAR(8), F.[Registration date])) BETWEEN p.Monthmin AND p.Monthmax THEN F.[Supplier number] ELSE NULL END ) AS [Number of New Suppliers], p.Period FROM [dbo].[Purchase] AS F CROSS JOIN cte_Periods AS p WHERE F.[Purchase date] >= p.Monthmin AND F.[Purchase date] <= p.Monthmax GROUP BY p.Period;

Best regards,
Rubrix

Re: How to reference a column from a virtual table to be used in a IF statement ?

lbendlin — Sat, 06 May 2023 22:01:46 GMT

Virtual tables don't have a selected value. Use an aggregation that makes sense for your scenario, like MINX(cte_periods,[Monthmin]).

topic How to reference a column from a virtual table to be used in a IF statement ? in DAX Commands and Tips

How to reference a column from a virtual table to be used in a IF statement ?

Re: How to reference a column from a virtual table to be used in a IF statement ?