Power Query - Need HLP

[KIL2022]

Hello,

i m looking for solution for Power Query:

1. Lookup Value - Same Table or Different Table

I have a formaula, but i get information back,  - Circle reference
Idea behind this is DAX = LOOKUPVALUE('Sales Account'[ID2],'Sales Account'[accountid],'Sales Account'[id])
____

(let

searchValue = [id]

in

Table.SelectRows(

    #"Sales Account",

    [accountid]=searchValue)

){0}[ID2]

2. Countif (without merging Tables)?

var sItem= Table1[Item]

var sVisit= Table1[Visit]

CALCULATE(COUNT(Table1[Item]),FILTER(Table1,Table1[Item.]=sItem),FILTER(Table1,Table1[Visit]=sVisit))

3. IF MAXX?

var sItem= Table1[Item]

var sVisit= Table1[Visit]
var sCreated = Table1[Created at]

IF(MAXX(FILTER(Table1,Table1[Visit]=sVisit&& Table1[Item]=sItem),Table1[Created at)=sCreated ,1,0)

[Anonymous · Accepted solution]

Hi @KIL2022 ,

1.The lookupvalue operation can use the merge function for match filtering. Please refer to

Merge queries overview - Power Query | Microsoft Learn

Solved: How to LOOKUPVALUE in PowerQuery? - Microsoft Fabric Community

2.The countif operation can achieved by group by and custom column. Please refer to

Power Query: COUNTIFS - Microsoft Community Hub

Solved: A simple way for COUNTIF function in power query - Power Platform Community (microsoft.com)

3.To filter the max value group by [visit] and [item], you can use group by feature and custom column.

Group by two fields:

Expand:

Create the custom column:

Best Regards,

Stephen Tao

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