Lookup in a Tabular Model with direct query

[Anonymous]

Hi there, 
I am new to the Tabular Model and relatively new to DAX.

I do have a tabular model, and I want to calculate this

Stage (from 'PipelineStages'[Stage]	SalesFunnel (SUM(_RequestbyStage[RowCount]))	Pct of previous
Registration	500	blank
Enquiry	479	95.8 %  --> 479 / 500
Visited	33	6.9 % --> 33/479
Lost	400	80.0 % !!! Lost/Registration 400/500

The issue is using ChatGPT; when I try just to get the SalesFunnel value of the above row, I either get

a value on every row which is identical to the SalesFunnel of the same line:

IF ( 
     CALCULATETABLE( 
                    VALUES(_PipelineStages[Stage]); _PipelineStages[Stage] = "Lost" 
                           ) = "Lost"; 
                    CALCULATE([Sales Funnel]); BLANK() 
)

just blanks:

IF (
    SELECTEDVALUE(_PipelineStages[Stage]) = "Lost";
    CALCULATE([Sales Funnel]);
    BLANK()
)

or it yields an error "a table of multiple values war supplied where a single value was expected":

IF (
    VALUES(_PipelineStages[Stage]) = "Lost",
    [Sales Funnel],
    BLANK()
)

or there seems something wrong with RELATEDTABLE(PipelineStages)[Stage] Syntax or it could be that it's because this table is invisible:

IF (
    RELATEDTABLE(_PipelineStages)[Stage] = "Lost";
    CALCULATE([Sales Funnel]);
    BLANK()
)

any help is highly welcome. Even if the finding is this is impossible in a tabular model with a direct query using PowerBI to debug. 

Thanks, 
Matthias

[Anonymous]

Hi @Anonymous 

Thank you very much for your answer.
Yes, sorry if that was confusing. I just wanted to show everything that came back from ChatGPT, which did not work.

I have a table with a dimension with members a to d, splitting up a measure.
Now I want to calculate for every row the percentage of the current row's measure value divided by the previous row, except for the last row

Dimension	Measure	Pct
a	100
b	98	b/a
c	40	c/b
d	30	d/a

How can I look up the value of the previous or the first row without making the table dimension header filter out the value of the other row?
I have a Tabular Model and Direct Query. The measure is the count of a table, and it is not the same table the dimension is coming from. Is this possible in PowerBI on a Tabular Model, or would I need a calculated column?

I hope this makes my use case more evident.

Best,
Matthias

[Anonymous]

Hi @Anonymous 

Use the following code in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0MFCK1YlWSgKyLS3AzGQg0wQimgJkGgOZsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dimension = _t, Measure = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dimension", type text}, {"Measure", Int64.Type}}),
    RowCount = Table.RowCount(Source),
    
    SourceWithIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
   
    Custom = Table.AddColumn(SourceWithIndex, "CustomMeasure", each 
        let
            currentIndex = [Index],
            currentMeasure = [Measure],
            prevMeasure = if currentIndex > 0 then SourceWithIndex{currentIndex - 1}[Measure] else null,         
            firstMeasure = SourceWithIndex{0}[Measure],      
            lastMeasure = SourceWithIndex{RowCount - 1}[Measure],     
            result = if currentIndex = RowCount - 1 then currentMeasure / firstMeasure else currentMeasure / prevMeasure
        in
            result, type number),
    #"Filtered Rows" = Table.SelectRows(Custom, each true)
in
    #"Filtered Rows"

This is the result you want

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous 

Thank you a lot for coming back to me.
In the meantime, I could solve the calculation partly using DAX with the new table calculation capabilities using OFFSET. I did not want to work with additional tables or columns.

Var PrevValue = CALCULATE([Sales];
                                    OFFSET(-1; 
                                    ALLSELECTED(DimTab[Rank];DimTab[Dim]);  
                                    OrderBy(DimTab[Rank]; ASC))
                                    ) 

This works perfectly fine.
Thank you again for your help.

[Anonymous]

Hi @Anonymous

Can you clearly describe your needs? From the four dax formulas you gave, I can only know that these four formulas are trying to achieve the same goal.

From the dax formula you gave, I can give you a few suggestions

1.There is a problem with the arguments in the CALCULATETABLE function, This is the correct usage of the expression

CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, …]]])

CALCULATETABLE function (DAX) - DAX | Microsoft Learn

2.The argument in the calculate function cannot be a column, This is the correct usage of the expression 

CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]]) 

CALCULATE function (DAX) - DAX | Microsoft Learn

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.