Table visuals report

[bzeeblitz]

I need to create table visuals report which meets the condition whencreated yesterday and onhand>9months from the below reports

Table Name :cycle;Note:Multiple times Item# found
Item#	Order:Card Type	PO#
1-UPRIGHT	Card	21390
1-UPRIGHT	card	21390
1-UPRIGHT	card	21390
567890	Non card	21391
567890	non card	21391
567891	card	21392

Table Name : POItem;Note:Multiple times Item# found
Item#	Createdwhen	Vendor name
36 upright	PO not created yesterday	Welldone
36 upright	PO not created yesterday	welldone
36 upright	pO  created yesterday	welldone
38 upright	PO not created yesterday	vendor1

Table Name:Demand ;Note:No duplicate Item Number
Item Number	onhand>9 months	onhand>6 months
P123-AB-GG-AA-US	Less Than 9 months onhand	Less Than 6 months onhand
P123-AB-T-Z-US	more Than 9 months onhand	moreThan 6 months onhand
P123-E-BB-Z-US	Less Than 9 months onhand	Less Than 6 months onhand
215B14567	more Than 9 months onhand	more Than 6 months onhand

[DataNinja777]

Hi @bzeeblitz ,

To achieve the desired Power BI report that filters data based on the conditions Createdwhen = 'PO created yesterday' and onhand > 9 months, follow these steps using DAX. First, import the Cycle, POItem, and Demand tables into Power BI and establish relationships between them. Specifically, link Cycle[Item#] to POItem[Item#] and Cycle[Item#] to Demand[Item Number].

Next, create a calculated table named FilteredPOItem to filter the POItem table. Use the DAX formula:

FilteredPOItem = 
FILTER(
    POItem,
    LOWER(POItem[Createdwhen]) = "po created yesterday"
)

Similarly, create another calculated table named FilteredDemand to filter the Demand table. Use the formula:

FilteredDemand = 
FILTER(
    Demand,
    LOWER(Demand[onhand>9 months]) = "more than 9 months onhand"
)

After filtering the individual tables, create a combined calculated table that merges the filtered data from Cycle, FilteredPOItem, and FilteredDemand. Use the DAX formula:

FilteredTable = 
SUMMARIZECOLUMNS(
    Cycle[Item#],
    Cycle[Order:Card Type],
    Cycle[PO#],
    Demand[onhand>9 months],
    POItem[Vendor name],
    FILTER(
        FilteredPOItem,
        FilteredPOItem[Item#] = Cycle[Item#]
    ),
    FILTER(
        FilteredDemand,
        FilteredDemand[Item Number] = Cycle[Item#]
    )
)

Finally, add a table visual to the report and include the following columns: Cycle[Item#], Cycle[Order:Card Type], Cycle[PO#], Demand[onhand>9 months], and POItem[Vendor name]. This ensures the report dynamically filters and displays data meeting the specified conditions. Ensure the relationships in the model are correctly set up to enable filtering and cross-referencing between the tables. Let me know if any further assistance is needed.

Best regards,

[bzeeblitz]

could you please help me with below code for our above scenario for 3 tables and im able to build for 2 tables but i am not sure how to refine the below code to our above logic.

kindly help

Table =

SELECTCOLUMNS(

VALUES('Demand'[Item Number]), -- Ensure only unique Item Numbers

"Greater than 9 Months On-Hand",

CALCULATE(MAX('Demand'[Onhand>9months])),

"Greater than 6 Months On-Hand",

CALCULATE(MAX('Demand'[Onhand>6months])),

"Item Number",

'Demand'[Item Number],

"CreatedWhen",

FIRSTNONBLANK(

'poitem'[CreatedWhen], -- This should be a single column

'poitem'[CreatedWhen] -- We are evaluating CreatedWhen for non-blank values

)

)

[DataNinja777]

Hi @bzeeblitz ,

To refine your code to handle the scenario for three tables and meet your requirements, the logic must ensure that data from all three tables is integrated correctly while maintaining clarity and efficiency.

Below is the refined DAX formula to include logic for three tables, assuming the third table is called Orders with a relevant column Orders[OrderDate] that needs to be included in the calculation:

Table =
SELECTCOLUMNS(
    VALUES('Demand'[Item Number]), -- Ensure only unique Item Numbers
    "Greater than 9 Months On-Hand",
        CALCULATE(
            MAX('Demand'[Onhand>9months])
        ),
    "Greater than 6 Months On-Hand",
        CALCULATE(
            MAX('Demand'[Onhand>6months])
        ),
    "Item Number",
        'Demand'[Item Number],
    "CreatedWhen",
        FIRSTNONBLANK(
            'poitem'[CreatedWhen], -- Extract non-blank CreatedWhen
            'poitem'[CreatedWhen]
        ),
    "Order Date",
        FIRSTNONBLANK(
            'Orders'[OrderDate], -- Extract non-blank Order Date
            'Orders'[OrderDate]
        )
)

To enhance the integration of the third table, the Orders table has been included, specifically incorporating the Orders[OrderDate] column to ensure its relevant data is part of the calculation. Each column has been reviewed for clarity, ensuring meaningful names and corresponding values. The logic for handling non-blank values has been implemented using the FIRSTNONBLANK function to extract the first available non-blank value from the Orders table.

It is important to ensure that relationships between the Demand, poitem, and Orders tables are properly defined within the data model to facilitate accurate calculations. The columns [Onhand>9months] and [Onhand>6months] must already exist in the Demand table for this formula to function as intended. Additionally, verify that the CreatedWhen and OrderDate fields are correctly populated and mapped to their respective tables.

Best regards,

[bzeeblitz]

Third table is cycle but not order. So do we need to create relationship for these 3 tables before filter since we have duplicate itemno in poitel and cycle table and the reason is for creating table visuals while dragging columns we need such relationship.in my case when I create relationship between poitem table and on-demand it's saying item 100 is duplicate etc so how to handle this

[bzeeblitz]

im unable to create relatinships for below links as im getting error

Specifically, link Cycle[Item#] to POItem[Item#] -Error-"There is already a relationship between two columns"

Cycle[Item#] to Demand[Item Number].Error-"There is already a relationship between two columns"

[bzeeblitz]

FilteredDemand = 
FILTER(
    Demand,
    LOWER(Demand[onhand>9 months]) = "more than 9 months onhand"
)

im getting cannot find name error onhand>9 months in this line of code LOWER(Demand[onhand>9 months]) = "more than 9 months onhand"

ALso im getting error cannot find createdwhen column in

    LOWER(POItem[Createdwhen]) = "po created yesterday"

FilteredPOItem = 
FILTER(
    POItem,
    LOWER(POItem[Createdwhen]) = "po created yesterday"
)

[bzeeblitz]

Also createdwhen and Onhand>9 months  is a calculated field ,i mean this below formula is existed in this columns in demand table and poitem table

Onhand>9months = if('Demand'[Months of Available Inventory]>9,"More than 9 months On-Hand","Less than 9 months On-Hand")

CreatedWhen = if('POitem'[Date: Line Created].[Day] = day(TODAY()-1),"PO Created Yesterday","PO Not Created Yesterday")